Getting the most recent entry per group in a select statement
- by TheObserver
I have 3 tables to join to get table1.code, table1.series, table2.entry_date, table3.title1
and I'm trying to get the most recent non null table3.title1 grouped by table1.code and table1.series.
select table1.code, table1.series, max(table2.entry_date), table3.Title1
from table3 INNER JOIN table2 ON table3.ID = table2.ID
INNER JOIN table1 ON table2.source_code = table1.code
where table3.Title1 is not NULL
group by table1.code, table1.series, table3.Title1
seems to give me all entries with a non null title1 instead of the most recent one. How should I structure the query to just pick the newest version of Title1 per code & series?