TSQL selecting distinct based on the highest date

Question

Our database has a bunch of records that have the same invoice number, but have different dates and different notes.

so you might have something like

invoice    date         notes
 3622      1/3/2010     some notes
 3622      9/12/2010    some different notes
 3622      9/29/1010    Some more notes
 4212      9/1/2009     notes
 4212      10/10/2010   different notes

I need to select the distinct invoice numbers, dates and notes. for the record with the most recent date.

so my result should contain just

3622      9/29/1010    Some more notes
4212      10/10/2010   different notes

how is it possible to do this? Thanks!

Solution 1

select invoice, date, notes
from table 
inner join (select invoice, max(date) as date from table group by invoice) as max_date_table      
    on table.invoice = max_date_table.invoice and table.date = max_date_table.date

Solution 2

Use analytical functions :

WITH TT AS (
    SELECT invoice, date, notes, RANK() OVER(PARTITION BY invoice ORDER BY date DESC) AS R
    FROM table
)
SELECT invoice, date, notes
FROM TT
WHERE R = 1;

Solution 3

Try:

SELECT I.* 
FROM MyInvoice AS I
INNER JOIN 
           (SELECT Invoice, MAX([Date]) AS MaxDate
            FROM MyInvoice 
            GROUP BY Invoice
           ) AS M ON I.Date = M.MaxDate 
                 AND I.Invoice = M.Invoice

Comments

[Martin]

I think your inner query is wrong ... it will only return 1 row. Then the whole query will only return one row ...

[twal]

This did exactly what I needed it to do. Thank you very much!

[Maverick]

Thanks Vincent. Had been looking for this solution all day long. You made my day :)

[Emil]

Surprisingly, this question has relatively few views. This query has been one of the most useful answers that has helped me in many scenarios!