PostgreSQL -``令人震惊的ISTINCT ON''和``ROUP BY''语法

PostgreSQL -``令人震惊的ISTINCT ON''和``ROUP BY''语法

为了能够选择所有列，而不仅是

object_id

和

MAX(event_timestamp)

，您可以使用

DISTINCT ON

SELECt DISTINCT ON (object_id)     object_id, event_timestamp    ---, more columnsFROM test_select ORDER BY object_id, event_timestamp DESC ;

如果希望结果按

event_timestamp DESC

而不是按进行排序

object_id

，则需要将其包括在派生表或CTE中：

SELECT *FROM   ( SELECt DISTINCT ON (object_id)         object_id, event_timestamp    ---, more columns    FROM test_select     ORDER BY object_id, event_timestamp DESC   ) AS tORDER BY event_timestamp DESC ;

另外，您可以使用窗口功能，例如

ROW_NUMBER()

：

WITH cte AS  ( SELECT ROW_NUMBER() OVER (PARTITION BY object_id         ORDER BY event_timestamp DESC)   AS rn, object_id, event_timestamp    ---, more columns    FROM test_select   )SELECT object_id, event_timestamp    ---, more columnsFROM cteWHERe rn = 1ORDER BY event_timestamp DESC ;

或

MAX()

与

OVER

：

WITH cte AS  ( SELECt MAX(event_timestamp) OVER (PARTITION BY object_id)   AS max_event_timestamp, object_id, event_timestamp    ---, more columns    FROM test_select   )SELECT object_id, event_timestamp    ---, more columnsFROM cteWHERe event_timestamp = max_event_timestampORDER BY event_timestamp DESC ;