按test_date倒序筛选前100个唯一化合物的SQL实现

要实现每个compound_id仅返回一次，且优先展示拥有最新test_date的化合物，核心是先获取每个化合物对应的最新测试日期，再基于这个日期排序取前100个不重复的化合物。以下是两种可行的SQL写法：

方法一：GROUP BY聚合最新日期 #

通过分组聚合直接得到每个化合物的最新测试日期，再排序截取前100：

SELECT c.compound_id
FROM compounds c
INNER JOIN lots l ON c.compound_id = l.compound_id
INNER JOIN tests t ON l.lot_id = t.lot_id
GROUP BY c.compound_id
ORDER BY MAX(t.test_date) DESC NULLS LAST
LIMIT 100;

方法二：窗口函数去重 #

利用ROW_NUMBER()按化合物分组，给每个化合物的测试记录按日期降序编号，只保留每个化合物的第一条（即最新测试记录），再排序取前100：

WITH ranked_compounds AS (
    SELECT 
        c.compound_id,
        t.test_date,
        ROW_NUMBER() OVER (PARTITION BY c.compound_id ORDER BY t.test_date DESC NULLS LAST) AS rn
    FROM compounds c
    INNER JOIN lots l ON c.compound_id = l.compound_id
    INNER JOIN tests t ON l.lot_id = t.lot_id
)
SELECT compound_id
FROM ranked_compounds
WHERE rn = 1
ORDER BY test_date DESC NULLS LAST
LIMIT 100;

原代码问题说明 #

你之前的查询未按compound_id分组，DENSE_RANK()是对全局所有测试记录排序，导致同一个化合物对应多条重复行，无法实现去重。上述两种写法都先确保每个化合物仅保留一条对应最新测试日期的记录，再完成排序和截取。

内容的提问来源于stack exchange，提问作者beginner_