Return Max from SQL subset

Question

I have the following query, however there can be several rows within the accountoneway table which have the same data except the cost, however there is also a column for date_created.

What I need to do, is return the rows which have the max date_created for each subset

SELECT outlocation.LocationName as [Out Location]
    ,inlocation.LocationName as [In Location]
    ,cast(SUBSTRING(AccountOneWays.startdate,7,2)as char(2)) +'/'+
    cast(SUBSTRING(AccountOneWays.startdate,5,2)as char(2))+'/'+
    cast(SUBSTRING(AccountOneWays.startdate,1,4)as char(4)) as [Start Date]
    ,cast(SUBSTRING(AccountOneWays.enddate,7,2)as char(2)) +'/'+
    cast(SUBSTRING(AccountOneWays.enddate,5,2)as char(2))+'/'+
    cast(SUBSTRING(AccountOneWays.enddate,1,4)as char(4)) as [End Date]
    ,AccountOneWays.cost as [Cost]
    ,AccountOneWays.ID as [ID] 
FROM AccountOneWays 
    INNER JOIN Locations outlocation 
        on outlocation.ID = AccountOneWays.start_location 
    INNER JOIN Locations inlocation 
        on inlocation.ID = AccountOneWays.end_location
WHERE AccountOneWays.Account = (SELECT ID FROM Accounts WHERE Account = 'Alamo')  
AND AccountOneWays.start_location IN (
                                      SELECT ID 
                                      FROM locations 
                                      WHERE locationname IN ('Allentown Arpt')
                                     )
ORDER BY [End Date] DESC
    ,[Start Date] DESC
    ,[Out Location] ASC
    ,[In Location] ASC

Your help would be appreciated

Answer 1

You can use a ranking function like ROW_NUMBER:

WITH cte 
     AS (SELECT [Out Location] = outlocation.locationname,
                [In Location]  = inlocation.locationname,
                Cast(Substring(accountoneways.startdate, 7, 2)AS CHAR(2)) 
                + '/' 
                + Cast(Substring(accountoneways.startdate, 5, 2)AS CHAR(2)) 
                + '/' 
                + Cast(Substring(accountoneways.startdate, 1, 4)AS CHAR(4)) AS 
                   [Start Date], 
                Cast(Substring(accountoneways.enddate, 7, 2)AS CHAR(2)) 
                + '/' 
                + Cast(Substring(accountoneways.enddate, 5, 2)AS CHAR(2)) 
                + '/' 
                + Cast(Substring(accountoneways.enddate, 1, 4)AS CHAR(4))   AS 
                   [End Date], 
                Cost = accountoneways.cost,
                ID = accountoneways.id,
                RN = ROW_NUMBER() OVER (PARTITION BY AccountOneWays.ID
                                        ORDER BY  date_created DESC) 
         FROM   accountoneways 
                INNER JOIN locations outlocation 
                        ON outlocation.id = accountoneways.start_location 
                INNER JOIN locations inlocation 
                        ON inlocation.id = accountoneways.end_location 
         WHERE  accountoneways.account = (SELECT id 
                                          FROM   accounts 
                                          WHERE  account = 'Alamo') 
                AND accountoneways.start_location IN (SELECT id 
                                                      FROM   locations 
                                                      WHERE 
                    locationname IN ( 'Allentown Arpt'))
) 
SELECT *
FROM   CTE 
WHERE  RN = 1 
ORDER  BY [end date] DESC, 
          [start date] DESC, 
          [out location] ASC, 
          [in location] ASC 

This returns exactly one record for each ID even if there are multiple rows with the highest date_created. If you want to return all rows in then use DENSE_RANK instead of ROW_NUMBER.

Here you can see all functions and how they work: http://technet.microsoft.com/en-us/library/ms189798.aspx