SQL query to sort data by time and date and then select only the newest record

sql

I am trying to trying to update create a query that will sort data by Date and time, find the newest record and then update another field in the record marking it as so.

Take my life harder the time and data are two separate fields and the time is also a string.

So here is what I have so far,

UPDATE server.ESCC_HWAY_ASSETS_GULLIES_N 
set CURRENT_REC = 'Y' 
From server.ESCC_HWAY_ASSETS_GULLIES_N A 
inner join ( 
SELECT GULLY_ID, Max([DATE]) AS MaxDate, MAX([TIME]) AS MaxTime 
FROM server.ESCC_HWAY_ASSETS_GULLIES_N B
GROUP BY GULLY_ID, [DATE] ) B 
on A.GULLY_ID = B.GULLY_ID and A.[DATE] = B.MaxDate and A.[TIME] = B.MaxTime

This results in data that is sorted by time and date but it updates all fields it finds, apart from on dates where there are two entries. Then it only updates the newest record.

I am testing on a single record – B47605 which gives the following results on this queuer

SELECT GULLY_ID, Max([DATE]) AS MaxDate, MAX([TIME]) AS MaxTime 
FROM ESMAPADMIN.ESCC_HWAY_ASSETS_GULLIES_N B
WHERE GULLY_ID = 'B47605'
GROUP BY GULLY_ID, [DATE]

Gully_ID     MaxDate                MaxTime
B47605  2008-03-12 00:00:00.000 09:02:29
B47605  2008-09-19 00:00:00.000 09:51:14
B47605  2009-02-16 00:00:00.000 11:18:28
B47605  2009-08-21 00:00:00.000 12:34:45
B47605  2010-03-16 00:00:00.000 09:22:26
B47605  2010-08-25 00:00:00.000 11:19:55
B47605  2011-03-07 00:00:00.000 12:19:56
B47605  2012-05-02 00:00:00.000 20:57:54

The result I would like is to only have the newest record returned so –

Gully_ID     MaxDate                MaxTime
B47605  2012-05-02 00:00:00.000 20:57:54

I am not sure how to go from where i am to where i need to be, so any help would be appreciated.

Best Answer

Assuming you are using SQL Server 2005+ (because of the [] I see)

;WITH latestResult
AS
(
    SELECT Gully_ID, MaxDate, MaxTime,
            ROW_NUMBER() OVER (PARTITION BY Gully_ID
                                ORDER BY MaxDate DESC, MaxTime DESC) RN
    FROM tableName
)
SELECT Gully_ID, MaxDate, MaxTime
FROM latestResult
WHERE RN = 1
Related Topic