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