如何獲得每組第二高的價值？

從表中獲取第二高的值已經解決了很多次，但我正在尋找每個組中的第二高值。

鑑於此表：

+----+-----+
| A  |  10 |
| A  |  20 |
| A  |  35 |  <-- This record
| A  |  42 |
| B  |  12 |
| B  |  21 |  <-- This record
| B  |  33 |
| C  |  14 |
| C  |  23 |
| C  |  38 |
| C  |  41 |  <-- This record
| C  |  55 |
+----+-----+

我想得到標記的行。

虛擬碼：

select col_a, penultimate(col_b)
from foo
group by col_a;

您可以為此使用視窗函式。

select col_a, col_b
from (
 select col_a, 
        col_b, 
        dense_rank() over (partition by col_a order by col_b desc) as rnk
 from the_table
) t
where rnk = 2

假設每組不同的值。所以我們不需要斷絕關係。

假設每組至少有 2 行 - 或以下查詢中斷。（您需要做更多事情，首先為這些情況定義“第二高值”。）

每組多於幾行，（雖然該功能並未直接實現，但從第 14 頁開始）模擬索引跳過掃描將（快得多）。取第二高值有點棘手：

WITH RECURSIVE cte AS (
  (
  SELECT col_a, col_b
  FROM   tbl
  ORDER  BY col_a, col_b DESC
  OFFSET 1
  LIMIT  1
  )
  UNION ALL
  (
  SELECT t.col_a, t.col_b
  FROM   cte c
  JOIN   tbl t ON t.col_a > c.col_a
  ORDER  BY t.col_a, t.col_b DESC
  OFFSET 1
  LIMIT  1
  )
  )
TABLE cte;

db<>在這裡擺弄

看：

• SELECT DISTINCT 在我的 PostgreSQL 表上比預期的要慢

需要一個適用的索引才能快速。像：

CREATE UNIQUE INDEX ON tbl (col_a, col_b DESC);

Postgres 幾乎可以全速向後掃描索引。但是列的組合排序順序不能與查詢不一致。看：

• 優化對一系列時間戳（兩列）的查詢

引用自：https://dba.stackexchange.com/questions/303297