ROW_NUMBER SQL: Ranking, Deduplication, and Top N SQL ROW_NUMBER 完整指南：排名、去重与每组 Top N

Quick answer: what does ROW_NUMBER do in SQL? 快速答案：SQL 中的 ROW_NUMBER 是做什么的？

The function does not permanently number table rows and does not guarantee final display order. It calculates a value for the current result set. Use it when you need one winner per business key, the first few rows per group, numbered slices for a report, or an explicit row sequence for downstream logic.

该函数不会永久修改表中的行号，也不会保证最终展示顺序。它只为当前结果集计算一个临时值。需要为每个业务键选择唯一结果、提取每组前几条记录、生成报表分页区间或为后续逻辑提供明确顺序时，都可以使用它。

ROW_NUMBER() OVER (
  PARTITION BY group_column
  ORDER BY sort_column DESC, unique_id
) AS row_num

Understand the ROW_NUMBER syntax before using it 使用 ROW_NUMBER 前先理解语法

Four decisions control the result. The input rowset decides what can be numbered. PARTITION BY decides where numbering restarts. The window ORDER BY decides which row comes first. A tie-breaker decides whether that order is reproducible. Leaving any decision implicit can produce a query that runs successfully but answers the wrong question.

结果由四个决定共同控制：输入行集决定哪些记录参与编号， PARTITION BY 决定编号从哪里重新开始，窗口内的 ORDER BY 决定谁排在前面，而并列破局字段决定结果是否能够稳定复现。任何一项含糊，都可能让查询“能运行但答案错误”。

Make ROW_NUMBER deterministic when ties exist 存在并列值时让 ROW_NUMBER 保持确定性

Suppose two status updates for the same ticket share the same updated_at value. If the window orders only by that timestamp, either update may receive row number 1. The result can change after a new execution plan, parallel processing, a maintenance operation, or simply a different physical row order. The SQL is valid, but the winner is not defined.

假设同一工单的两次状态更新具有相同的 updated_at 。如果窗口只按时间戳排序，任意一条都可能得到编号 1。执行计划变化、并行处理、维护操作或物理行顺序变化，都可能改变结果。SQL 语法没有错，但“获胜者”没有被定义。

-- Unstable when updated_at contains ties
ROW_NUMBER() OVER (
  PARTITION BY ticket_id
  ORDER BY updated_at DESC
)

-- Stable if event_id is unique and immutable
ROW_NUMBER() OVER (
  PARTITION BY ticket_id
  ORDER BY updated_at DESC, event_id DESC
)

A good tie-breaker is non-null, stable, and unique within the partition. A primary key is often suitable. Do not append a random function merely to silence ties; that explicitly makes selection variable. Also remember that the window ordering controls numbering, not final presentation. Add a query-level ORDER BY when consumers need the output itself sorted.

理想的破局字段应当非空、稳定，并且在分区内唯一。主键通常是合适选择。不要为了消除并列而加入随机函数，那会主动制造不稳定性。同时要记住：窗口内排序只控制编号，不控制最终展示顺序；如果下游要求结果排序，还应在查询末尾增加外层 ORDER BY 。

ROW_NUMBER vs RANK vs DENSE_RANK ROW_NUMBER、RANK 与 DENSE_RANK 的区别

Choose the function according to the meaning of a tie. For values 100, 90, 90, and 80 ordered descending, ROW_NUMBER produces 1, 2, 3, 4; RANK produces 1, 2, 2, 4; and DENSE_RANK produces 1, 2, 2, 3. The functions are not interchangeable when a boundary such as “top three” is involved.

应根据并列的业务含义选择函数。对按降序排列的 100、90、90、80， ROW_NUMBER 产生 1、2、3、4； RANK 产生 1、2、2、4； DENSE_RANK 产生 1、2、2、3。当存在“前三名”等边界时，它们不能互换。

The PostgreSQL reference defines ROW_NUMBER as the current row's number within its partition and distinguishes it from peer-aware ranking. Use ROW_NUMBER when the requirement is exactly N physical rows or one selected row. Use a peer-aware function when equal business values must share rank.

PostgreSQL 官方参考将 ROW_NUMBER 定义为当前行在分区内的编号，并将其与考虑并列关系的排名函数区分开来。业务要求“恰好 N 条物理记录”或“只保留一条”时使用 ROW_NUMBER ；业务上相等的值必须共享名次时，则使用考虑并列的函数。

A complete ROW_NUMBER SQL example 一个完整的 ROW_NUMBER SQL 示例

Assume orders contains one row per order. The report needs each customer's orders from newest to oldest and must behave predictably when two orders share a timestamp. The combination of order_date and unique order_id defines the sequence.

假设 orders 每个订单一行。报表需要按从新到旧展示每位客户的订单，并且在两个订单时间相同时仍保持稳定。此时由 order_date 与唯一的 order_id 共同定义顺序。

SELECT
  order_id,
  customer_id,
  order_date,
  amount,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY order_date DESC, order_id DESC
  ) AS customer_order_number
FROM orders
WHERE order_status = 'completed'
ORDER BY customer_id, customer_order_number;

The WHERE clause removes non-completed orders before numbering, so the sequence describes completed orders only. PARTITION BY customer_id restarts at 1 for each customer. The two ordering columns make the order total. Finally, the outer ORDER BY makes the delivered result readable; it does not change the row numbers already calculated.

WHERE 会在编号前排除未完成订单，因此序列只描述已完成订单。 PARTITION BY customer_id 让每位客户重新从 1 开始。两个排序字段共同形成完全顺序，最外层 ORDER BY 仅让结果便于阅读，不会改变已经计算出的编号。

Return the top N rows per group 使用 ROW_NUMBER 返回每组 Top N

A global LIMIT returns N rows for the entire result. To return exactly N rows for every customer, product, or region, calculate a partitioned row number first and filter it outside. This is one of the clearest reasons to use a window function.

全局 LIMIT 只会为整个结果返回 N 行。如果需要为每个客户、产品或地区恰好返回 N 行，应先计算分区编号，再在外层过滤。这是使用窗口函数最典型的理由之一。

WITH ranked_orders AS (
  SELECT
    o.*,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY amount DESC, order_id
    ) AS rn
  FROM orders o
  WHERE order_status = 'completed'
)
SELECT *
FROM ranked_orders
WHERE rn <= 3;

This returns a maximum of three rows per customer. It deliberately breaks equal amounts using order_id . If the requirement says “include every order tied for third-highest amount,” use RANK or DENSE_RANK and accept that a customer may return more than three rows. Document that distinction because both interpretations are plausible.

该查询每位客户最多返回三行，并用 order_id 打破金额并列。如果业务要求“包含所有与第三高金额并列的订单”，则应使用 RANK 或 DENSE_RANK ，并接受每位客户可能返回超过三行。两种解释都合理，因此必须记录清楚。

Deduplicate rows and keep the intended winner 去重并保留业务上正确的记录

“Remove duplicates” is incomplete as a requirement. You must define the duplicate key and the winner. For a customer profile history table, the key might be customer_id ; the winner might be the greatest updated_at , followed by the greatest ingestion identifier when timestamps tie. ROW_NUMBER expresses that policy directly.

“删除重复项”并不是完整需求。必须同时定义重复键与获胜规则。对于客户资料历史表，重复键可能是 customer_id ；胜出记录可能是 updated_at 最大者，时间相同时再选择摄取标识最大的记录。 ROW_NUMBER 可以直接表达这一政策。

WITH candidates AS (
  SELECT
    p.*,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY updated_at DESC, ingestion_id DESC
    ) AS rn
  FROM customer_profile_history p
)
SELECT *
FROM candidates
WHERE rn = 1;

Do not use this pattern to hide unexplained duplication introduced by an incorrect join. First measure the row count and key uniqueness before and after each join. If one order becomes five rows because it matches five line items, ranking one row does not repair the grain—it merely discards information according to a possibly unrelated order.

不要用这种模式掩盖错误连接造成的重复。应先测量每次连接前后的行数与键唯一性。如果一个订单因为匹配五条明细而变成五行，排名后只留一行并没有修复粒度，只是按照可能无关的顺序丢弃信息。

• Define the duplicate key independently of the winner rule.
• Make the winner rule deterministic and explain why it matches the business.
• Count partitions with more than one candidate before discarding rows.
• Retain rejected records or audit counts when deletion has governance impact.

• 分别定义重复键和获胜规则，不要把两者混为一谈。
• 让获胜规则具有确定性，并解释为何符合业务。
• 丢弃记录前，统计候选行超过一条的分区数量。
• 当删除影响治理时，保留被淘汰记录或审计计数。

Use ROW_NUMBER for pagination with clear tradeoffs 使用 ROW_NUMBER 分页并理解取舍

Numbered pagination can return a requested range and is useful for reports that must jump directly to a page. The ordering must be stable, and the snapshot must be consistent if users expect page boundaries not to move while data changes.

编号分页可以返回指定区间，适合必须直接跳转到某页的报表。排序必须稳定；如果用户希望数据变化期间分页边界不移动，还需要一致的读取快照。

WITH numbered AS (
  SELECT
    order_id,
    order_date,
    customer_id,
    amount,
    ROW_NUMBER() OVER (
      ORDER BY order_date DESC, order_id DESC
    ) AS rn
  FROM orders
)
SELECT *
FROM numbered
WHERE rn BETWEEN 101 AND 125
ORDER BY rn;

The apparent simplicity can hide cost: the engine may sort and number every qualifying row before it returns a small page. For deep, frequently requested pages on large changing tables, keyset pagination often performs more predictably by requesting rows after the last seen ordering key. Keyset pagination cannot jump to an arbitrary page as naturally, so the choice depends on product behavior, not syntax preference.

这种写法表面简单，但可能隐藏较大成本：数据库可能先排序并编号所有符合条件的行，最后才返回一个很小的页面。对于频繁访问的大型动态表，键集分页通常更稳定，因为它直接请求上次排序键之后的记录。但键集分页不适合自然跳转到任意页，因此选择应由产品行为决定，而非语法偏好。

Place filters and joins at the correct grain 在正确粒度放置过滤与连接

The same ROW_NUMBER expression can answer different questions depending on filter placement. Filtering to completed orders before numbering means “latest completed order.” Numbering all orders and filtering status afterward means “latest order, but return it only if completed.” Those results diverge whenever a customer's newest order is pending.

同一个 ROW_NUMBER 表达式会因过滤位置不同而回答不同问题。先过滤已完成订单再编号，表示“最新的已完成订单”；先为全部订单编号、外层再过滤状态，表示“最新订单，并且仅当它已完成时返回”。当客户最新订单仍待处理时，两者结果完全不同。

1. State the decision. Write whether eligibility is determined before ranking or whether rank is assigned across the full population.
2. Establish the row grain. Confirm what one row represents before any one-to-many join.
3. Apply eligibility filters. Put filters inside or outside the ranked CTE according to the stated decision.
4. Join attributes deliberately. If possible, rank to one row per key before joining to dimensions that should not multiply rows.
5. Recount and test. Validate row counts, distinct keys, ties, null keys, and missing relationships.

1. 明确决策。 记录资格条件是在排名前确定，还是先对完整总体排名。
2. 确定行粒度。 在任何一对多连接前，确认一行代表什么实体。
3. 放置资格过滤。 根据决策，把过滤条件放在排名 CTE 内部或外部。
4. 谨慎连接属性。 如果可以，先把每个键排名到一行，再连接不应增加行数的维表。
5. 重新计数与测试。 检查行数、唯一键、并列、空键和缺失关系。

Recognize when ROW_NUMBER adds query complexity 识别 ROW_NUMBER 带来的查询复杂度

ROW_NUMBER itself is compact. Complexity comes from the work needed to produce the correct ordered partitions and from the layers built around the function. Large sorts, skewed partitions, repeated windows, wide rows, nested CTEs, and joins that expand the input can make a short-looking expression expensive or difficult to verify.

ROW_NUMBER 本身很短。真正的复杂度来自生成正确有序分区所需的工作，以及函数周围叠加的查询层。大规模排序、分区倾斜、重复窗口、宽行、嵌套 CTE 和扩张输入的连接，都可能让看似简短的表达式代价高昂且难以验证。

Indexes that begin with selective filter columns and continue with partition and ordering columns may reduce work in some databases and plans, but no universal index recipe exists. Inspect the execution plan with realistic data. Check sort operations, memory spills, estimated versus actual rows, partition skew, and whether an early filter or narrower projection changes the plan safely.

某些数据库和执行计划可以利用以选择性过滤字段开头、随后包含分区与排序字段的索引来减少工作，但不存在通用索引公式。应使用真实规模数据检查执行计划，关注排序、内存溢写、估算与实际行数、分区倾斜，以及提前过滤或缩窄字段是否能安全改变计划。

Check ROW_NUMBER behavior across SQL dialects 检查不同 SQL 方言中的 ROW_NUMBER

PostgreSQL, SQL Server, MySQL 8+, Oracle, Snowflake, BigQuery, and other modern platforms support ROW_NUMBER , but surrounding syntax and optimizer behavior differ. SQL Server requires an ORDER BY in the window specification. MySQL documents that omitting it makes numbering nondeterministic. Some cloud warehouses support QUALIFY , which filters window results without an extra visible CTE; PostgreSQL, SQL Server, and MySQL commonly use a subquery or CTE instead.

PostgreSQL、SQL Server、MySQL 8+、Oracle、Snowflake、BigQuery 等现代平台均支持 ROW_NUMBER ，但周边语法与优化器行为不同。SQL Server 要求窗口定义中包含 ORDER BY ；MySQL 文档说明省略排序会使编号不确定。部分云数据仓库支持 QUALIFY ，可以不增加可见 CTE 就过滤窗口结果；PostgreSQL、SQL Server 与 MySQL 通常使用子查询或 CTE。

For authoritative syntax, consult the Microsoft SQL Server ROW_NUMBER documentation and the MySQL window-function reference . Test the exact production engine and version rather than assuming a portable query has identical performance everywhere.

权威语法可参考 Microsoft SQL Server ROW_NUMBER 文档与 MySQL 窗口函数参考。即使查询语法可移植，也不应假设各平台性能相同；请在实际生产引擎和版本上测试。

Validate a ROW_NUMBER query before production 上线前验证 ROW_NUMBER 查询

Correct sample output is not enough. A ranking query should survive adversarial data, changes in volume, and a second reviewer who can reconstruct the business rule from the SQL.

样例输出正确并不足够。排名查询必须能够应对极端数据、数据量变化，并让第二位审查者可以从 SQL 还原业务规则。

1. Name the grain. Write what one input row and one output row represent.
2. State eligibility. Decide which filters apply before ranking and which conditions apply after it.
3. Test partitions. Include empty relationships, one-row groups, large groups, null partition keys, and skewed tenants.
4. Force ties. Create duplicate primary sort values and confirm the unique tie-breaker selects the intended row.
5. Check boundaries. Test N-1, N, and N+1 candidates for top-N logic and the first/last rows of every page.
6. Audit joins. Compare total rows and distinct business keys before and after each join.
7. Inspect the plan. Use representative volume to review sorts, spills, row estimates, and repeated window work.
8. Review the complete SQL. Evaluate the function together with CTEs, joins, projections, filters, and downstream assumptions.

1. 命名粒度。 写明一条输入记录和一条输出记录分别代表什么。
2. 声明资格。 区分排名前过滤条件和排名后筛选条件。
3. 测试分区。 覆盖空关系、单行分组、大分组、空分区键与租户倾斜。
4. 制造并列。 创建主要排序值重复的数据，确认唯一破局字段选中预期记录。
5. 检查边界。 对 Top-N 测试 N-1、N、N+1 条候选，并测试每页首尾记录。
6. 审计连接。 比较每次连接前后的总行数与不同业务键数量。
7. 检查执行计划。 用代表性数据量查看排序、溢写、行数估算与重复窗口工作。
8. 审查完整 SQL。 将函数与 CTE、连接、字段投影、过滤和下游假设放在一起评估。

ROW_NUMBER SQL frequently asked questions ROW_NUMBER SQL 常见问题

Continue with a related guide 继续阅读相关指南

Use these closely related guides to verify the next layer of the workflow without mixing separate troubleshooting or decision intents. 可继续阅读以下紧密相关的指南，验证工作流的下一层，同时避免混淆不同的排障或决策意图。

• SQL window functions SQL 窗口函数
• PARTITION BY in SQL SQL PARTITION BY
• RANK in SQL SQL RANK