Sort partitions with several criteria

Question

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;

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:

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

Answer 1

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.