Sort by maximum value per partition [closed]

Question

I have a table with data something like below:

| ID(pk) | name | count |
|--------|------|-------|
| id1    | xyz  |    98 |
| id2    | ptr  |     5 |
| id3    | xyz  |     2 |
| id4    | de   |     1 |
| id5    | ptr  |     1 |

I want to first have the row with max count then all the rows with same name sorted by count. After that next max count and all the counts with same name. Something like below:

| ID(pk) | name | count |
|--------|------|-------|
| id1    | xyz  |    98 |
| id3    | xyz  |     2 |
| id2    | ptr  |     5 |
| id5    | ptr  |     1 |
| id4    | de   |     1 |

Is something like this possible in Postgres?

Answer 1

It seems you want to order by the maximum count for the same name first - which can be achieved with a basic window function.

SELECT *
FROM   tbl
ORDER  BY max(count) OVER (PARTITION BY name) DESC, count DESC;

fiddle

Notably, the window function also works in the ORDER BY clause as demonstrated.