TSQL selecting distinct based on the highest date

t-sql

Solution

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

Problem

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!

Original source