在SQL中为数据块postgreusing ORDER BY

Question

我想知道如何在SQL查询中对数据进行排序，但只能对某些块进行排序。我将提供一个例子，使它更容易。

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

在上面的例子中，我想做一个ORDER BY高度DESC，但是只有每个级别中最高的人会被排序，而同一级别中的其他人都在按身高ASC排序的人下面。所以我想要的最终结果是：

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

所以汤姆是最高的，所以他上升到了最高的位置，自然而然地，除了安排ASC之外，其他人都在他下面。约翰是剩下的人中最高的，所以他和他的团队排在下一位。我可以使用什么样的查询来实现这一点？

Answer 1

我会将其表述为：

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

首先确定每个rank的冠军

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

根据冠军确定每个等级的排名

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

返回两个CTE以获得您指定的订单。当被命令将冠军置于每个rank分组的顶部时，rm.rank_height != h.height利用了false在true之前的事实。

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;

正如Gordon Linoff所指出的，这可以简化为只使用窗口函数：

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

更新了Working Fiddle。

Answer 3

尝试按升序和降序进行排序，然后将其包装在case语句中，如果排名靠前，则选择降序，否则使用升序(在升序上加1以避免重叠(。

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

您可以使用窗口函数，例如ROW_NUMBER。ROW_NUMBER()是一个窗口函数，它为结果集分区内的每一行分配一个顺序整数。

您将获得所有行的编号(在每个级别内，按高度升序排列(和每个级别的最大高度值。为了获得正确的顺序，只需将数字替换为最大高度的值为0，其他人的停留时间不变：

如果您需要列通过以下方式订购：

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

或者只需要按特定顺序对行进行排序：

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