SQL Tip : Maximum date via left join

Selecting records by their maximum date via a left join.

select * from maintable m

left join (

select id
, max(somedate) as mydate
from sometable
group by id
, mydate

) s on m.id = s.id

Whats going on?

1.) select * from maintable m
Select all the records from your base table.

2.) left join
Bring back all matching records

3.) select id, max(somedate) from sometable group by id, mydate
Select the id and date field you wish query the group by is needed to only bring back one record per id and because we are using an aggregate function the sql would error without it

4.) s on m.id = s.id
Here’s where the magic happens: select * from customersales left join (select id, max(lastsaledate) as dtmDate from customersales group by id, dtmDate) sales on sales.id = customersales.id voila you have all the most recent customer sales and a join away from all the customer information. You could also use an inner join to only bring back customers with sales and only their most recent sale.

eg.
select * from customersales
inner join (select id, max(lastsaledate) as dtmDate from customersales group by id, dtmDate) sales on sales.id = customersales.id
inner join customer c on c.id = customersales.customerid