SQL如何按DISTINCT列值分组获取限定数量的排序行

现有一张结构如下的数据表：

id | value |       date         |  for  | unit
----------------------------------------------
17 | 49.0 | 2021-02-22 10:00:00 | Chest | cm
29 | 49.0 | 2021-02-22 10:00:00 | Hip   | cm
14 | 49.0 | 2021-02-21 10:00:00 | Chest | cm
16 | 48.0 | 2021-02-21 09:00:00 | Chest | cm
26 | 49.0 | 2021-02-21 10:00:00 | Waist | cm
28 | 48.0 | 2021-02-21 10:00:00 | Hip   | cm
27 | 48.0 | 2021-02-20 10:00:00 | Waist | cm
13 | 49.0 | 2021-02-06 10:00:00 | Chest | cm
25 | 49.0 | 2021-02-06 10:00:00 | Hip   | cm
12 | 48.0 | 2021-02-05 10:00:00 | Chest | cm

期望查询返回如下结果集：

id | value |       date         |  for  | unit
----------------------------------------------
17 | 49.0 | 2021-02-22 10:00:00 | Chest | cm
14 | 49.0 | 2021-02-21 10:00:00 | Chest | cm
29 | 49.0 | 2021-02-22 10:00:00 | Hip   | cm
28 | 48.0 | 2021-02-21 10:00:00 | Hip   | cm
26 | 49.0 | 2021-02-21 10:00:00 | Waist | cm
27 | 48.0 | 2021-02-20 10:00:00 | Waist | cm

核心需求：针对for列的每个不同值，返回按date列降序排序后的前2条对应记录。

主流数据库通用写法（支持MySQL 8.0+、PostgreSQL、SQL Server、Oracle） #

使用窗口函数ROW_NUMBER()实现分组排序取Top N，逻辑清晰且性能更好。注意for是SQL保留字，编写时需要根据你用的数据库加转义符（MySQL用反引号，PostgreSQL、SQL Server用双引号）：

SELECT id, value, date, `for`, unit
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY `for`
            ORDER BY date DESC, id DESC -- 加id DESC作为同日期下的兜底排序规则，避免结果随机
        ) AS row_rank
    FROM 你的实际表名 -- 替换为你自己的表名
) t
WHERE row_rank <= 2
ORDER BY `for`, date DESC;

兼容MySQL 5.x等不支持窗口函数的旧版本写法 #

如果你的数据库版本不支持窗口函数，可以用关联子查询实现相同逻辑：

SELECT t1.*
FROM 你的实际表名 t1
WHERE (
    SELECT COUNT(*)
    FROM 你的实际表名 t2
    WHERE t2.`for` = t1.`for`
      AND (t2.date > t1.date OR (t2.date = t1.date AND t2.id >= t1.id))
) <= 2
ORDER BY t1.`for`, t1.date DESC;

说明：如果同一个for分类下存在多条date完全相同的记录，上述写法会结合id做排序兜底，保证每个分类严格返回2条记录，不会出现多返或者少返的情况。

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