utilisation de ORDER BY dans SQL pour des morceaux de données

Question

Je veux savoir comment je trie les données dans une requête SQL, mais uniquement dans certains segments. Je vais vous donner un exemple pour vous faciliter la tâche.

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

Dans l'exemple ci-dessus, je veux faire un ORDER BY hauteur DESC, MAIS seule la personne la plus grande de chaque rang est ordonnée et tous les autres dans le même rang sont juste sous cette personne classée par hauteur ASC. Donc, le résultat final que je veux est:

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

Donc, Tom est le plus grand, alors il monte en haut, et automatiquement tout le monde dans son rang passe sous lui mais arrangé ASC. John est le plus grand des autres, alors lui et son groupe passent ensuite. Quelle est la meilleure requête que je puisse utiliser pour y parvenir?

Answer 1

Déterminez d'abord le champion de chaque rank

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

Déterminez le classement de chaque rang par champion

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

Rejoignez les deux CTE pour obtenir la commande que vous avez spécifiée. Le rm.rank_height != h.heightprofite du fait qui falsevient avant truequand on lui ordonne de placer le champion en tête de chaque rankgroupement.

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;

Comme l'a souligné Gordon Linoff, cela peut être simplifié comme suit en utilisant uniquement les fonctions de fenêtre:

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

Mise à jour du violon de travail.

Answer 2

Je formulerais ceci comme:

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

Essayez de classer à la fois par ordre croissant et décroissant, puis enveloppez-le dans une instruction case pour choisir l'ordre décroissant s'il est le mieux classé, sinon utilisez l'ordre croissant (ajoutez 1 à l'ordre croissant pour éviter les chevauchements).

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

Vous pouvez utiliser la fonction de fenêtre, telle que ROW_NUMBER. La ROW_NUMBER()est une fonction de fenêtre qui affecte un entier séquentiel à chaque ligne dans la partition d'un jeu de résultats.

Vous obtenez des nombres pour toutes les lignes (dans chaque rang et ordre par hauteur croissant) et la valeur de la hauteur maximale pour chaque rang. Et pour un ordre correct, remplacez simplement le nombre par la valeur de la hauteur maximale à 0, les autres restent sans changer:

Si vous avez besoin d'une colonne à commander par:

    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 juste besoin de classer les lignes dans un ordre spécifique :

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