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:
开发者_如何学C 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, Mark
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)
精彩评论