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:

  • TSQL: Prevent trigger suppressing error but rolling back transaction
  • No fields for dynamic SQL stored procedure in SSRS with SET FMTONLY
  • Multiple textbox insert into to sql table in separate rows using LINQ
  • SQL Server Ratio SQL request with 2 decimal after comma
  • Getting column names from a table
  • Left Excluding join not working
  •           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

    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)
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.