Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Window Functions

SQL ROW_NUMBER() function

Reference for SQL ROW_NUMBER(): number rows 1, 2, 3 within a window, top-N per group, removing duplicates, pagination and why a tie-breaker matters.

ROW_NUMBER() is a window function that gives each row a unique sequential number, starting at 1, in the order defined by the ORDER BY inside its OVER() clause. With PARTITION BY the numbering restarts for every partition.

Unlike RANK(), it never produces ties: rows with equal sort values still get different numbers, in an order the database chooses unless you add more columns to the ORDER BY. That makes it the standard tool for "top N per group", for picking one row per key and for removing duplicates.

SQL
SELECT first_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS position
FROM employees;

Try it

SQL Numbering all rows and numbering inside groups ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL Top 2 products per category ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Keeping one row per customer, and paging ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
ROW_NUMBER() OVER (
  [ PARTITION BY expression [, ...] ]
  ORDER BY expression [ ASC | DESC ] [, ...]
)
Part Description
(no arguments) The parentheses after ROW_NUMBER are always empty.
PARTITION BY Optional. Numbering restarts at 1 in each partition.
ORDER BY Defines the numbering order. Technically optional in most databases, but then the order is arbitrary; SQL Server and Oracle require it.

Returns: a positive integer (bigint in several databases). It is never NULL.

Dialect differences

Feature SQLite PostgreSQL MySQL SQL Server Oracle
ROW_NUMBER() 3.25+ 8.4+ 8.0+ 2005+ Yes
OVER without ORDER BY Allowed Allowed Allowed Error Error
Pseudo-column alternative rowid (not the same thing) None None None ROWNUM (assigned before sorting)
SQL
-- SQL Server: a constant ORDER BY when you do not care about the order
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n, name FROM products;

-- Oracle: ROWNUM is numbered before ORDER BY, so this does NOT give the top 3
SELECT * FROM products WHERE ROWNUM <= 3 ORDER BY price DESC;

Usage notes

  • Add a tie-breaker. If two rows have the same ORDER BY value, which one gets the lower number is unspecified and may change between runs. End the ORDER BY with a unique column such as id.
  • You cannot filter on it directly. A window function is not allowed in WHERE. Put the query in a subquery or CTE and filter on the alias outside, as in the examples above. (Some databases offer QUALIFY for this; SQLite, PostgreSQL, MySQL, SQL Server and Oracle do not.)
  • Top N per group: rn <= N after partitioning. If ties at the boundary should all be kept, use RANK() instead.
  • Deduplicating: number the rows within each duplicate key, then keep rn = 1, or delete the rows with rn > 1.
  • Pagination: LIMIT ... OFFSET is simpler for plain paging. Row numbers are useful when the page is cut from an already-numbered result.
  • Not related to rowid. rowid is a stored identity of a row in SQLite; ROW_NUMBER() is computed per query.
  • Cost: the database sorts rows by the partition and order columns. An index in that order can avoid the sort.