Distinct item with latest timestamp and multiple (or all) columns returned.

Question

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, Mark

Answer 1

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)

Answer 2

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]
  )