using ORDER BY in SQL for chunks of data

Question

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 1

I would phrase this as:

select t.*
from (select t.*,
             max(height) over (partition by rank) as max_height
      from t
     ) t
order by max_height,
         rank,
         (height = max_height)::int desc,   -- put the largest heights first
         height desc;

Answer 2

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.

Answer 3

Try ordering by both ascending and descending, then wrap it in a case statement to chose descending order if its the top ranked, else use the ascending order (add 1 to the ascending order to avoid overlap).

SELECT  a.*, CASE hgt_desc
                WHEN 1 THEN hgt_desc
                ELSE hgt_asc
             END AS new_rank 
FROM    (
        SELECT  *, 
            ROW_NUMBER() OVER (PARTITION BY rank ORDER BY height ASC) + 1 AS hgt_asc, 
            ROW_NUMBER() OVER (PARTITION BY rank ORDER BY height DESC) AS hgt_desc 
        FROM    table 
        ) AS a 
ORDER BY a.rank, new_rank 

Answer 4

You can use window function, such as ROW_NUMBER. The ROW_NUMBER() is a window function that assigns a sequential integer to each row within the partition of a result set.

You are getting numbers for all rows (within each rank and order by height ascending) and max height's value for every rank. And for correct order, just replace number with max height' s value to 0,others's stay without changing:

If you need column to order by:

    Select *, case when height=max_val then 0 else num end as order_column from
    (
    --get the max height's value and order by height asc within each rank
    Select *, max(height) over(partition by rank) max_val ,row_number () over(partition by rank order by height) num
    from Table
    ) X
Order by rank asc,order_column asc

Or just need ordering rows in specific order:

Select * from
(
--get the max height's value and order by height asc within each rank
Select *, max(height) over(partition by rank) max_val ,row_number () over(partition by rank order by height) num
from Table
) X
Order by rank asc, 
Case (when height=max_val then 0 else num end ) asc