PostgreSQL如何筛选USER_ID=1且SORT_ID最小的行的MY_DATE

问题场景 #

现有数据表 MY_TABLE 数据如下：

ID     SORT_ID     MY_DATE       USER_ID
10       1         01/01/2023      1
11       2         01/09/2023      1
13       3         15/08/2023      1
14       4         10/12/2023      1

需要筛选出 USER_ID=1 时，SORT_ID 最小的行（即 SORT_ID=1、ID=10 的行）对应的 MY_DATE。

原查询返回了 SORT_ID 最大的行数据：

SELECT DISTINCT ON (USER_ID)
MY_DATE 
FROM MY_TABLE where USER_ID = 1
ORDER BY USER_ID;

正确查询调整方案 #

修改 ORDER BY 子句，在 USER_ID 后添加 SORT_ID ASC，让 DISTINCT ON 明确取 SORT_ID 最小的行：

SELECT DISTINCT ON (USER_ID)
MY_DATE 
FROM MY_TABLE where USER_ID = 1
ORDER BY USER_ID, SORT_ID ASC;

原理说明 #

在PostgreSQL中，DISTINCT ON 的逻辑是：按照 ORDER BY 的顺序，对每个 DISTINCT ON 指定的字段分组后，取每组的第一行数据。原查询仅按 USER_ID 排序，分组后行的顺序未明确，数据库会返回任意一行（这里恰好是最后插入的行）。添加 SORT_ID ASC 后，分组内的行按 SORT_ID 升序排列，第一行就是 SORT_ID 最小的目标行。

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