I want to know how I sort data in a SQL query but only in certain chunks. I’ll provide an example to make it easier.
--------------------------- | height | rank | name | ----------------------------- | 172 | 8 | Bob | ----------------------------- | 183 | 8 | John | ----------------------------- | 185 | 2 | Mitch | ----------------------------- | 179 | 2 | Sarah | ----------------------------- | 154 | 8 | Martha | --------------------------- | 190 | 2 | Tom | ---------------------------
In the above example, I want to do an ORDER BY height DESC, BUT only the tallest person of each rank gets ordered and everyone else in the same rank is right under that person ordered by height ASC. So the end result I want is:
--------------------------- | height | rank | name | --------------------------- | 190 | 2 | Tom | ----------------------------- | 179 | 2 | Sarah | ----------------------------- | 185 | 2 | Mitch | ----------------------------- | 183 | 8 | John | ----------------------------- | 154 | 8 | Martha | ---------------------------- | 172 | 8 | Bob | ---------------------------
So Tom is the tallest, so he goes up top, and automatically everyone everyone else in his rank goes underneath him but arranged ASC. John is tallest of the remaining so he and his group go next. What is the best query I can use to accomplish this?
Advertisement
Answer
First determine the champion of each rank
with rank_max as (
select rank, max(height) as rank_height
from heights
group by rank
),
Determine the ranking for each rank by champion
rank_ranking as (
select rank,
dense_rank() over (order by rank_height desc) as rank_rank
from rank_max
)
Join back to both CTEs to get the ordering you specified. The rm.rank_height != h.height takes advantage of the fact that false comes before true when ordered to put the champion at the top of each rank grouping.
select h.*
from heights h
join rank_ranking r on r.rank = h.rank
join rank_max rm on rm.rank = h.rank
order by r.rank_rank,
rm.rank_height != h.height,
h.height;
As pointed out by Gordon Linoff, this can be simplified to the following using only window functions:
select *
from heights
order by max(height) over (partition by rank) desc,
max(height) over (partition by rank) != height,
height;