Getting the most recent entry per group in a select statement

Question

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?

Answer 1

Try this:

select table1.code, table1.series, max(table2.entry_date), max(table3.Title1) as 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 
And Table2.entry_Date = (Select Max(sq.entry_Date)
                        From sq.table2
                        Where sq.id = table2.ID)
group by table1.code, table1.series

Answer 2

Maybe something like this to join on only the most recent of the table2 entries?

SELECT
    table1.code,
    table1.series,
    table2.entry_date,
    table3.Title1
FROM
    table1
INNER JOIN
    table2
ON
    table2.source_code = table1.code
AND
    table2.entry_date =
(
    SELECT
        MAX(maxtable2.entry_date)
    FROM    
        table2 maxtable2
    WHERE
        maxtable2.source_code = table2.source_code
)
INNER JOIN
    table3
ON
    table3.ID = table2.ID

Answer 3

SELECT  t1.code, 
        t1.series, 
        max(t2.entry_date), 
        t3.Title1
FROM table1 t1

    INNER JOIN table2 t2 ON t2.source_code = t1.code 
    INNER JOIN table3 t3 ON t3.ID = t2.ID 
    AND t3.ID = (
                    SELECT MAX(t3copy.ID)
                    FROM table3 t3copy
                    WHERE t3.ID = t3copy.ID
                    AND t3copy.Title1 IS NOT NULL)
GROUP BY t1.code, 
        t1.series,
        t3.Title1

Answer 4

;with tlb as
(
    select table1.code, table1.series, table2.entry_date, table3.Title1,
    row_number() over(code, series, entry_date order by code, series, entry_date desc) as rn
    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
)
select * from tlb where rn = 1

This can be very quick depending on your indexes.