database - postgresql 选择一列中具有最大值的所有行

Question

我是 postgresql 的新手，我在 postgresql 中有一个像这样的表 T:

C1 C2 C3 C4 ID

C1,C2,C4 是整数。
C3 是一个字符C4 本质上是版本号。并且可以是 1-N 之间的任何值。
(例如，在一组插入事件之后，表中的值被更新。)ID 是一个字符。

问题:
对于给定的 ID 值，我想选择具有最高 C4 的所有行。例如表中可能有 N 个版本，我想要版本N对应的所有结果。

我试过了，

从 T 中选择 C1、C2、C3、max(C4)，其中 ID = 'something'；

但它给我错误。

请指教。

Answer 1

postgresql 版本 13+ 的更新

最新版postgresql介绍limit ... with ties

SELECT id, c1, c2, c3, c4
FROM t
WHERE id = 'something'
ORDER BY c4 DESC
FETCH FIRST 1 ROW WITH TIES

指定 WITH TIES 将返回所有 c4 等于该列中最大值的行，因此不需要带有窗口函数的单独 CTE。如果只有 1 行 c4 具有最大值，则只会返回 1 行。

---

原始答案(对于早期版本的 postgresql):

如果同一版本号可以有多行，则在cte中使用窗口函数RANK，选择rank等于1的行

SELECT id, c1, c2, c3, c4 
FROM (
    SELECT 
      *
    , RANK() OVER (PARTITION BY id ORDER BY c4 DESC) c4rank
    FROM t
) ranked 
WHERE c4rank = 1
  AND id = 'something'

如果您想要所有 ID 的最新版本，只需省略上述语句中的条件 id = 'something'。

如果给定版本号只能有 1 行，则使用 order by 和 limit 1

SELECT id, c1, c2, c3, c4
FROM t
WHERE id = 'something'
ORDER BY c4 DESC
LIMIT 1

如果您想要所有 id 的最新版本并且每个(id，版本)组合只能有 1 行

SELECT DISTINCT ON (id) id, c1, c2, c3, c4
FROM t
WHERE id = 'something'
ORDER BY id, c4 DESC