Distinct item with latest timestamp and multiple (or all) columns returned
I have a table delivery_history which is populated by a trigger:
id(guid) order_number delivery_number haulier timestamp(getdate()) 1 1234 haulier1 2009-10-08 8:34:00 2 1235 haulier1 2009-10-09 9:15:00 1 1234 haulier2 2009-10-10 9:20:00
I would like to return all the columns, but I only want to see the unique delivery_number with the latest timestamp, so the data I’d want returned would be:
1 1234 haulier2 2009-10-10 9:20:00 2 1235 haulier1 2009-10-09 9:15:00
So far I’ve tried every single possibility and combination of responses posted on this site and they either don’t give me the results I require, they expect that I’ve been sensible and used an incremental id rather than the guid I used (there’s now a weeks worth of data from the trigger and I don’t really want to dump it), or they expect Sql Server 2005 or higher.
This will be run on Sql Server 2000.
Thanks in advance,
2 Solutions collect form web for “Distinct item with latest timestamp and multiple (or all) columns returned”
I don’t like the potential for duplicates here. Two rows with the same delivery_number could have the same timestamp. It may be unlikely, given the frequency of deliveries and the behavior of your application, but I wouldn’t want to rely on that for correctness.
This version uses TOP 1 and allows an arbitrary number of tie-breakers:
select * from delivery_history a where id = ( select top 1 id from delivery_history b where a.delivery_number = b.delivery_number order by b.timestamp desc [, tie breaking col1, col2, ... colN] )
SELECT order_number, delivery_number, haulier, timestamp FROM delivery_history dh1 WHERE timestamp = (SELECT max(dh2.timestamp) FROM delivery_history dh2 WHERE dh2.delivery_number = dh1.delivery_number)