Home Ask Login Register

Developers Planet

Your answer is one click away!

ChrisCurrie February 2016

SQL to extract distinct values from an UNION query ordered by values that are not required

I am trying to get the TOP 20 DISTINCT address records from a query with a UNION...

The issue is that I want to order by date first - so the customer sees the most recent - but each date makes the row unique and leaves me with a ton of replica addresses (please see below).

SELECT 
    CB.CustomerGuid, CB.DisplayAddress, CB.LocatorId 
FROM 
    (SELECT 
         B.CustomerGuid, CAST(B.PickupDateTime AS DATE) AS TravelDate, 
         B.PickupDisplayAddress AS DisplayAddress, B.PickupAddressId AS LocatorId
     FROM 
         Bookings B
     WHERE 
         CustomerGuid = '463a20f2-a874-4964-865d-70d71065a69b'

     UNION 

     SELECT 
         B2.CustomerGuid, CAST(B2.PickupDateTime AS DATE) AS TravelDate, 
         B2.DestinationDisplayAddress AS DisplayAddress, B2.PickupAddressId AS LocatorId
     FROM 
         Bookings B2
     WHERE 
         CustomerGuid = '463a20f2-a874-4964-865d-70d71065a69b'
     ORDER BY 
         TravelDate DESC) AS CB

I felt the approach would be make the union query a subquery and query that (without the date) as DISTINCT but I have since learned that the order of rows in the result set is ultimately controlled by the ORDER BY clause in the outer SELECT. This therefore gives me the error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

How do I get around this please?

Answers


JJ32 February 2016

Something like this I think would work, assuming it doesn't matter which traveldate you sort by:

SELECT TOP 20 CB.CustomerGuid, CB.DisplayAddress, CB.LocatorId FROM 
(SELECT B.CustomerGuid, CAST(B.PickupDateTime AS DATE) AS TravelDate, 
B.PickupDisplayAddress AS DisplayAddress, B.PickupAddressId AS LocatorId
FROM Bookings B
WHERE CustomerGuid = '463a20f2-a874-4964-865d-70d71065a69b'
UNION 
SELECT B2.CustomerGuid, CAST(B2.PickupDateTime AS DATE) AS TravelDate, 
B2.DestinationDisplayAddress AS DisplayAddress, B2.PickupAddressId AS LocatorId
FROM Bookings B2
WHERE CustomerGuid = '463a20f2-a874-4964-865d-70d71065a69b'
) AS CB
GROUP BY CB.CustomerGuid, CB.DisplayAddress, CB.LocatorId
ORDER BY MAX(TravelDate) DESC

Post Status

Asked in February 2016
Viewed 1,502 times
Voted 14
Answered 1 times

Search




Leave an answer


Quote of the day: live life