usando ORDER BY em SQL para blocos de dados

Question

Quero saber como classifico os dados em uma consulta SQL, mas apenas em determinados blocos. Vou dar um exemplo para ficar mais fácil.

---------------------------
| height  |  rank  | name  |
-----------------------------
| 172  |  8     |   Bob    |
-----------------------------
| 183  |  8     |   John   |
-----------------------------
| 185  |  2     |   Mitch  |
-----------------------------
| 179 |   2     |   Sarah  |
-----------------------------
| 154   |  8    |   Martha |
---------------------------
| 190   |  2    |   Tom    |
---------------------------

No exemplo acima, quero fazer um ORDER BY height DESC, MAS apenas a pessoa mais alta de cada classificação é ordenada e todos os outros na mesma classificação estão logo abaixo dessa pessoa ordenada pela altura ASC. Então o resultado final que eu quero é:

---------------------------
| height  |  rank  | name  |
---------------------------
| 190   |  2    |   Tom    |
-----------------------------
| 179 |   2     |   Sarah  |
-----------------------------
| 185  |  2     |   Mitch  |
-----------------------------
| 183  |  8     |   John   |
-----------------------------
| 154  |  8     |   Martha |
----------------------------
| 172   |  8    |   Bob   |
---------------------------

Então Tom é o mais alto, então ele sobe, e automaticamente todos os outros em sua classificação vão abaixo dele, mas arranjados ASC. John é o mais alto dos restantes, então ele e seu grupo são os próximos. Qual é a melhor consulta que posso usar para fazer isso?

Answer 1

Primeiro determine o campeão de cadarank

with rank_max as (
  select rank, max(height) as rank_height
    from heights
   group by rank
),

Determine a classificação para cada classificação por campeão

 rank_ranking as (
  select rank, 
         dense_rank() over (order by rank_height desc) as rank_rank
    from rank_max
)

Junte-se a ambos os CTEs para obter a ordem que você especificou. O rm.rank_height != h.heightaproveita o fato que falsevem antes truequando manda colocar o campeão no topo de cada rankagrupamento.

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;

Conforme apontado por Gordon Linoff, isso pode ser simplificado para o seguinte usando apenas funções de janela:

select *
  from heights
 order by max(height) over (partition by rank) desc,
          max(height) over (partition by rank) != height,
          height;

Fiddle de trabalho atualizado.

Answer 2

Eu expressaria isso como:

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 3

Tente ordenar por ordem crescente e decrescente e, em seguida, envolva-o em uma instrução de caso para escolher a ordem decrescente se for a melhor classificada; caso contrário, use a ordem crescente (adicione 1 à ordem crescente para evitar sobreposição).

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

Você pode usar a função de janela, como ROW_NUMBER. A ROW_NUMBER()é uma função de janela que atribui um número inteiro sequencial a cada linha dentro da partição de um conjunto de resultados.

Você está obtendo números para todas as linhas (dentro de cada classificação e ordem crescente de altura) e o valor máximo da altura para cada classificação. E para a ordem correta, basta substituir o valor do número da altura máxima para 0, os outros permanecem sem alterar:

Se você precisar de coluna para ordenar por:

    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

Ou apenas precisa ordenar as linhas em uma ordem específica :

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