sql 排序后进行分组

可以使用子查询或者CTE（公共表表达式）来实现排序后分组。

使用子查询：

SELECT t1.group_id, t1.name, t1.salary
FROM (
  SELECT group_id, name, salary,
         ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY salary DESC) AS rn
  FROM employee
) t1
WHERE t1.rn = 1

使用CTE：

WITH cte AS (
  SELECT group_id, name, salary,
         ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY salary DESC) AS rn
  FROM employee
)
SELECT group_id, name, salary
FROM cte
WHERE rn = 1