usando ORDER BY en SQL para fragmentos de datos

Question

Quiero saber cómo ordeno los datos en una consulta SQL, pero solo en ciertos fragmentos. Voy a poner un ejemplo para que sea más fácil.

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

En el ejemplo anterior, quiero hacer un ORDEN POR altura DESC, PERO solo se ordena a la persona más alta de cada rango y todos los demás en el mismo rango están justo debajo de esa persona ordenada por altura ASC. Así que el resultado final que quiero es:

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

Así que Tom es el más alto, por lo que sube a la cima, y ​​automáticamente todos los demás en su rango van debajo de él, pero organizan ASC. John es el más alto de los restantes, por lo que él y su grupo son los siguientes. ¿Cuál es la mejor consulta que puedo usar para lograr esto?

Answer 1

Primero determine el campeón de cadarank

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

Determinar la clasificación para cada rango por campeón

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

Vuelva a unirse a ambos CTE para obtener el orden que especificó. El rm.rank_height != h.heightaprovecha que falseviene antes truecuando ordena poner al campeón en lo más alto de cada rankagrupación.

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;

Como señaló Gordon Linoff, esto se puede simplificar a lo siguiente usando solo funciones de ventana:

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

Violín de trabajo actualizado.

Answer 2

Yo expresaría esto 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

Intente ordenar tanto de forma ascendente como descendente, luego envuélvalo en una declaración de caso para elegir el orden descendente si es el mejor clasificado, de lo contrario, use el orden ascendente (agregue 1 al orden ascendente para evitar la superposición).

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

Puede usar la función de ventana, como ROW_NUMBER. es una función de ventana ROW_NUMBER()que asigna un entero secuencial a cada fila dentro de la partición de un conjunto de resultados.

Obtiene números para todas las filas (dentro de cada rango y orden por altura ascendente) y el valor de altura máxima para cada rango. Y para el orden correcto, simplemente reemplace el número con el valor de altura máxima a 0, los demás permanecen sin cambiar:

Si necesita una columna 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

O simplemente necesita ordenar filas en un orden específico :

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