postgreSQL for sort within group show top within group in Po

Question

我有一个表在Postgres有这样的数据:

fk | priority | date
1       1       4-1-2023
1       1       4-3-2023
1       2       4-5-2023
2       3       4-1-2023
2       2       4-2-2023
3       3       4-6-2023

我需要结果是这样的:

fk | priority | date
1       1       4-3-2023
2       2       4-2-2023
3       3       4-6-2023

所以在每组fk中，我想要最高的优先级。1是最高的。如果有优先顺序，那么我想要最近的日期。

我的SQL不是这么先进，我很难找到一个解决方案。提前谢谢。

Answer 1

这可以通过带有子查询的窗口函数来完成(适用于多个RDBMS)

SELECT fk, priority, mydate
FROM (
SELECT *, /* Return all the columns we want in the final query */
ROW_NUMBER() OVER (PARTITION BY fk ORDER BY priority, mydate DESC) AS rn
/* 
Generate a row number, 
restarting the numbering for each `fk` value seen (partition by)
Order the rows, first: the smallest number `priority` (ascending),
then: the latest `mydate` (descending)
Put the result in a column named `rn` to use in the outer query
*/
FROM mytable
) x
WHERE rn = 1 /* Filter by the `rn` value, return only the first row */
;

或者使用DISTINCT ON子句(更特定于postgresql)

SELECT DISTINCT ON (fk) * /* Return a distinct row for each fk value */
FROM mytable
ORDER BY fk, priority, mydate DESC 
/* 
Order by which is the first row to return 
It is required to include `fk` first because of the DISTINCT ON (fk), 
but then: get the smallest `priority` (ascending),
then: the latest `mydate` (descending).
*/
;