SELECT DISTINCT ON，按另一列排序

請考慮下表test：

CREATE TABLE test(col1 int, col2 varchar, col3 date);
INSERT INTO test VALUES
 (1,'abc','2015-09-10')
, (1,'abc','2015-09-11')
, (2,'xyz','2015-09-12')
, (2,'xyz','2015-09-13')
, (3,'tcs','2015-01-15')
, (3,'tcs','2015-01-18');

postgres=# select * from test;
 col1 | col2 |    col3    
------+------+------------
    1 | abc  | 2015-09-10
    1 | abc  | 2015-09-11
    2 | xyz  | 2015-09-12
    2 | xyz  | 2015-09-13
    3 | tcs  | 2015-01-15
    3 | tcs  | 2015-01-18

我想要一個按日期順序排列的返回集：

col1 | col2 |    col3    
------+------+------------
   2 | xyz  | 2015-09-13
   1 | abc  | 2015-09-11
   3 | tcs  | 2015-01-18

我設法完成的工作distinct on：

select distinct on (col1) col1, col2, col3 from test order by col1, col3 desc;
col1 | col2 |    col3    
------+------+------------
   1 | abc  | 2015-09-11
   2 | xyz  | 2015-09-13
   3 | tcs  | 2015-01-18

而不是我需要的having：

select distinct on (col1) col1, col2, col3 from test group by col1, col2, col3 having col3 = max(col3)
col1 | col2 |    col3    
------+------+------------
   1 | abc  | 2015-09-10
   2 | xyz  | 2015-09-13
   3 | tcs  | 2015-01-18

您仍然可以使用DISTINCT ON. 只需將其包裝到外部查詢中即可根據您的需要進行排序。看：

• 在一列上獲得不同，按另一列排序
• 具有不同 ORDER BY 的 PostgreSQL DISTINCT ON

SELECT *
FROM  (
  SELECT DISTINCT ON (col1)
         col1, col2, col3
  FROM   test
  ORDER  BY col1, col3 DESC
  ) sub
ORDER  BY col3 DESC, col2;

假設在col2功能上取決於col1，所以我們可以在內部查詢DISTINCT ON中忽略它。ORDER BY但我將它添加到外部ORDER BY作為有意義的決勝局。如果col2沒有 不是唯一的col1，您可能會col1另外附加。

假設col3被定義NOT NULL。其他附加NULLS LAST：

• PostgreSQL 按日期時間 asc 排序，先為空？

每行只有幾(col1)行，這通常是最快的解決方案。看：

• 在每個 GROUP BY 組中選擇第一行？

db<>在這裡擺弄

帶有視窗函式的子查詢row_number()（如 Vérace 建議的）是一種有效的替代方法，但通常速度較慢。我做了很多測試，但你自己試試。它必須排序兩次，就像DISTINCT ON（如果預期會更快，它可能會在內部切換到散列算法），但它保留內部查詢之後的所有行，增加了不必要的成本。無論哪種方式，您都不需要ORDER BY內部查詢：

SELECT col1, col2, col3
FROM  (
  SELECT col1, col2, col3
      ,  row_number() OVER (PARTITION BY col1 ORDER BY col3 DESC) AS rn
  FROM   test
  ) sub
WHERE  rn = 1
ORDER  BY col3 DESC, col2;

如果不需要，請不要使用 CTE。它通常要貴得多（直到 Postgres 12，主要是修復了這個問題）。

對於每行很多col1，索引變得更加重要，並且通常有更快的替代方案。看：

• 優化 GROUP BY 查詢以檢索每個使用者的最新行

此外，與 Oracle 或 SQL Server 不同，PostgreSQL 不使用術語“分析函式”來表示視窗函式。（關於這些功能的“分析”是什麼？）

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