中文
devexamples

Window Function Rank Dense Row Number

Compare RANK, DENSE_RANK, and ROW_NUMBER semantics on tied values, picking the right one for leaderboard and quota queries.

SqlData Integrity

Code

snippet
-- 三种排序语义在同一数据上的行为差异
WITH scores AS (
  SELECT * FROM (VALUES
    ('ada', 98), ('grace', 98), ('linus', 95),
    ('margaret', 92), ('katherine', 92), ('alan', 88)
  ) AS t(player, points)
)
SELECT
  player,
  points,
  ROW_NUMBER() OVER (ORDER BY points DESC) AS row_num,
  RANK()       OVER (ORDER BY points DESC) AS rank_no,
  DENSE_RANK() OVER (ORDER BY points DESC) AS dense_no
FROM scores
ORDER BY points DESC, player;

-- 每组前 N（配额类查询的标准写法）
WITH ranked AS (
  SELECT
    region,
    salesperson,
    revenue,
    ROW_NUMBER() OVER (
      PARTITION BY region ORDER BY revenue DESC
    ) AS rn
  FROM quarterly_revenue
)
SELECT region, salesperson, revenue
FROM ranked
WHERE rn <= 2
ORDER BY region, revenue DESC;

Related Examples