using ORDER BY in SQL for chunks of data

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?

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;

Updated Working Fiddle.