CODEWITHSUNDEEP
postgresqlpostgresql-8.4
Skartt
Skartt

Reputation: 591

Sort partitions with several criteria

I try to sort partitions with several criteria.

With this query, I have the following output:

SELECT id, aggregate_id, aggregate_update, detection_time
FROM report.report_table R1
WHERE table_name NOT LIKE 'AGGREGATE_ALERT_EVENT'
ORDER BY MAX(aggregate_update)
OVER (PARTITION BY aggregate_id) ASC,
  aggregate_id, aggregate_update asc, detection_time desc;

partition sorted by aggregate_update but not detection_time

We see that the rows are partitioned by aggregate_id. Inside each partition, the rows are sorted firstly by aggregate_update ASC, and then by detection_time DESC. However, the partitions are sorted only by MAX(aggregate_update), and I want the partition sorted by MAX(aggregate_update) and MAX(detection_time) DESC. I try to get the following result:

partition sorted by aggregate_update but and detection_time

How can I sort the partitions themselves between them with several criterias ?

Upvotes: 1

Views: 150

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521674

I think this should give you the behavior you want:

SELECT id, aggregate_id, aggregate_update, detection_time
FROM report.report_table R1
WHERE table_name NOT LIKE 'AGGREGATE_ALERT_EVENT'
ORDER BY
    MAX(aggregate_update) OVER (PARTITION BY aggregate_id),
    MAX(detection_time) OVER (PARTITION BY aggregate_id) DESC,
    aggregate_id,
    detection_time DESC;

The second sorting condition I added breaks the tie should two aggregate_id groups happen to have the same maximum update value. In this case, the sort falls back to whichever group has the greater detection time to decide which comes first.

Upvotes: 1

Related Questions