sql - 在选择语句中获取每组的最新条目

Question

我有 3 个表要加入以获得 table1.code、table1.series、table2.entry_date、table3.title1我正在尝试获取按 table1.code 和 table1.series 分组的最新非空 table3.title1。

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

似乎给了我所有带有非空 title1 而不是最近的条目。我应该如何构造查询以仅选择每个代码和系列的最新版本的 Title1？

Answer 1

试试这个:

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