如何编写PostgreSQL查询实现指定分类排序分组，让Mid类置顶

需求说明 #

需要查询tbl_practice表，实现两个核心要求：

• Mid分类的记录始终排在结果最顶部
• 每个分类内部的记录按created字段降序排列，保留每条记录的原始创建时间

原查询问题分析 #

你之前使用的窗口函数查询存在两个问题：

1. max(created) over (partition by category)会将每个分类下所有记录的created字段替换为该分类的最大创建时间，无法保留单条记录的原始时间值
2. 未指定自定义排序规则，导致Mid分类默认按字符串排序落在末尾

正确查询语句 #

SELECT category, created
FROM tbl_practice
ORDER BY 
    -- 给Mid分类设置最高排序优先级，确保置顶
    CASE WHEN category = 'Mid' THEN 0 ELSE 1 END,
    -- 保证同一分类的记录聚合在一起
    category,
    -- 分类内按创建时间降序排列
    created DESC;

查询逻辑说明 #

• 第一排序条件：通过CASE表达式给Mid分类标记为0，其他分类标记为1，数值小的排在前面，实现Mid置顶
• 第二排序条件：按category字段聚合，确保同一分类的记录连续排列
• 第三排序条件：按created字段降序，让同一分类内的记录从最新到最旧排列

执行结果 #

执行上述查询后，将得到与你预期完全一致的结果：

category  created
---------------------------------
Mid       2022-12-20 15:35:01.000
Mid       2022-10-20 15:35:01.000
Luxary    2022-09-21 15:35:01.000
Luxary    2022-09-20 15:35:01.000

内容的提问来源于stack exchange，提问作者Suddat Shende