By Matthew Rathbone · November 18, 2026

ROW_NUMBER() is a SQL window function that numbers the rows of a result in the order you choose. The first row gets 1, the next 2, and so on, with no gaps and no repeats. Add PARTITION BY and the count starts again at 1 for every group.

ROW_NUMBER() OVER (
  [PARTITION BY column, ...]
  ORDER BY column [ASC | DESC], ...
)
  • OVER (...) is required. It tells the database how to number the rows.
  • ORDER BY inside OVER decides which row is number 1. SQL Server, Oracle, Snowflake and Databricks require it; the others allow it to be left out, but the numbers are then meaningless.
  • PARTITION BY is optional. It splits the rows into groups and numbers each group separately.

ROW_NUMBER() works in MySQL 8.0+, PostgreSQL, SQL Server, Oracle, SQLite 3.25+, MariaDB 10.2+, Snowflake, BigQuery, Redshift, Databricks and DuckDB.

Jump to: sample data · number every row · PARTITION BY · ties · filter on ROW_NUMBER · top N per group · latest row per group · remove duplicates · pagination · QUALIFY · vs RANK and DENSE_RANK · without ORDER BY · with GROUP BY · MySQL 5.7 · database differences · performance · mistakes · FAQ

Sample data

Every query below was run on SQLite 3.45 and the output is copied from the real result. Error messages come from PostgreSQL 16, and the QUALIFY examples were run on DuckDB.

CREATE TABLE employees (
  id INTEGER PRIMARY KEY, name TEXT, department TEXT, salary INTEGER
);
INSERT INTO employees VALUES
 (1, 'Maya',  'Sales',        62000),
 (2, 'Leo',   'Engineering',  98000),
 (3, 'Priya', 'Engineering', 105000),
 (4, 'Sam',   'Support',      48000),
 (5, 'Alice', 'Sales',        71000),
 (6, 'Jonas', 'Support',      48000),
 (7, 'Bella', 'Engineering',  98000);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY, customer TEXT, order_date DATE, amount INTEGER
);
INSERT INTO orders VALUES
 (101, 'acme',    '2026-09-02', 250),
 (102, 'globex',  '2026-09-05', 120),
 (103, 'acme',    '2026-10-11', 400),
 (104, 'initech', '2026-10-15',  90),
 (105, 'globex',  '2026-10-20', 310),
 (106, 'acme',    '2026-11-03', 180),
 (107, 'globex',  '2026-10-20', 310);

Two salary ties matter later: Leo and Bella both earn 98,000, and Sam and Jonas both earn 48,000. In orders, rows 105 and 107 are an accidental duplicate.

Number every row with ROW_NUMBER() OVER (ORDER BY ...)

Number the employees from the highest salary to the lowest:

SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       name, salary
FROM employees;
row_num | name  | salary
--------+-------+-------
1       | Priya | 105000
2       | Leo   | 98000
3       | Bella | 98000
4       | Alice | 71000
5       | Maya  | 62000
6       | Sam   | 48000
7       | Jonas | 48000
(7 rows)

Unlike GROUP BY, a window function doesn't collapse rows. You still get all seven employees, each with one extra column. The ORDER BY inside OVER only decides the numbering. It does not sort the output. Here's the same numbering with the output sorted by name:

SELECT ROW_NUMBER() OVER (ORDER BY salary DESC, id) AS row_num,
       name, salary
FROM employees
ORDER BY name;
row_num | name  | salary
--------+-------+-------
4       | Alice | 71000
3       | Bella | 98000
7       | Jonas | 48000
2       | Leo   | 98000
5       | Maya  | 62000
1       | Priya | 105000
6       | Sam   | 48000
(7 rows)

Priya is still number 1 because she has the highest salary. If you want the rows in numbered order, add an outer ORDER BY as well. Many databases happen to return them in that order, but nothing guarantees it.

ROW_NUMBER() OVER (PARTITION BY ...)

PARTITION BY restarts the count for each group. Number employees by salary inside each department:

SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;
name  | department  | salary | rn
------+-------------+--------+---
Priya | Engineering | 105000 | 1
Leo   | Engineering | 98000  | 2
Bella | Engineering | 98000  | 3
Alice | Sales       | 71000  | 1
Maya  | Sales       | 62000  | 2
Sam   | Support     | 48000  | 1
Jonas | Support     | 48000  | 2
(7 rows)

Each department has its own 1, 2, 3. Read PARTITION BY department ORDER BY salary DESC as "for each department, number the rows from highest to lowest salary". You can partition by several columns (PARTITION BY department, city), and the count restarts whenever any of them changes.

This is the pattern behind almost every practical use of ROW_NUMBER(): number the rows in each group, then keep the ones you want.

Ties: ROW_NUMBER() picks one arbitrarily

Leo and Bella earn the same salary, yet Leo got 2 and Bella got 3. Nothing in the query asked for that. When rows tie on every ORDER BY column, ROW_NUMBER() still has to give them different numbers, and which one wins is up to the database. It can change after an insert, an index change or an upgrade, and it can differ between databases. That makes reports flicker and "keep row 1" queries return a different row from one day to the next.

Fix it by adding a column that is unique as the last ORDER BY item, so there are no ties left:

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, name) AS row_num
FROM employees;
name  | salary | row_num
------+--------+--------
Priya | 105000 | 1
Bella | 98000  | 2
Leo   | 98000  | 3
Alice | 71000  | 4
Maya  | 62000  | 5
Jonas | 48000  | 6
Sam   | 48000  | 7
(7 rows)

Now Bella comes before Leo every time. Names can repeat in real data, so the primary key (id) is the safest tie-breaker. If tied rows should share a number instead, you want RANK() or DENSE_RANK(), not ROW_NUMBER().

Why you can't use ROW_NUMBER() in WHERE

The obvious way to keep the top earner in each department doesn't work:

SELECT name, department, salary
FROM employees
WHERE ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) = 1;
ERROR:  window functions are not allowed in WHERE

SQLite says misuse of window function ROW_NUMBER(), and SQL Server says Windowed functions can only appear in the SELECT or ORDER BY clauses. Using the alias fails too:

SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees
WHERE rn = 1;
ERROR:  column "rn" does not exist

Both fail for the same reason: the database runs WHERE before SELECT, and window functions are calculated in the SELECT step. When WHERE runs, the row numbers don't exist yet. The fix is to calculate the number in a subquery and filter in the outer query:

SELECT name, department, salary
FROM (
  SELECT name, department, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, name) AS rn
  FROM employees
) ranked
WHERE rn = 1;
name  | department  | salary
------+-------------+-------
Priya | Engineering | 105000
Alice | Sales       | 71000
Jonas | Support     | 48000
(3 rows)

One row per department: the highest-paid employee. Support has a tie, and the name tie-breaker picks Jonas. A CTE does the same job and reads more easily, so the rest of this guide uses one. Snowflake, BigQuery, Databricks and DuckDB also have a shortcut, QUALIFY.

Top N rows per group

Change rn = 1 to rn <= N to keep the top N in each group. The two highest-paid employees per department:

WITH ranked AS (
  SELECT name, department, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, name) AS rn
  FROM employees
)
SELECT department, name, salary, rn
FROM ranked
WHERE rn <= 2
ORDER BY department, rn;
department  | name  | salary | rn
------------+-------+--------+---
Engineering | Priya | 105000 | 1
Engineering | Bella | 98000  | 2
Sales       | Alice | 71000  | 1
Sales       | Maya  | 62000  | 2
Support     | Jonas | 48000  | 1
Support     | Sam   | 48000  | 2
(6 rows)

Notice that Leo is missing even though he earns the same as Bella. ROW_NUMBER() always returns exactly N rows per group (or fewer if the group is small). If everyone tied for 2nd place should appear, use RANK() in the same query. "Top N per group" is one of the most common SQL interview questions, and this is the standard answer. A plain LIMIT can't do it, because LIMIT applies to the whole result, not to each group.

Get the latest row per group

The same pattern finds each customer's most recent order. Sort newest first inside each customer and keep row 1:

WITH latest AS (
  SELECT customer, id, order_date, amount,
         ROW_NUMBER() OVER (PARTITION BY customer ORDER BY order_date DESC, id DESC) AS rn
  FROM orders
)
SELECT customer, id, order_date, amount
FROM latest
WHERE rn = 1;
customer | id  | order_date | amount
---------+-----+------------+-------
acme     | 106 | 2026-11-03 | 180
globex   | 107 | 2026-10-20 | 310
initech  | 104 | 2026-10-15 | 90
(3 rows)

Why not GROUP BY customer with MAX(order_date)? That gives you the date but not the rest of the row (the order id and amount), so you'd have to join back to orders, and you'd get two rows for globex because two orders share the latest date. ROW_NUMBER() returns exactly one full row per customer, and id DESC decides the tie. Swap DESC for ASC to get each customer's first order instead.

The same idea works for "current price per product", "latest status per ticket" or "most recent login per user". In PostgreSQL, DISTINCT ON is a shorter alternative; ROW_NUMBER() works everywhere.

Find and remove duplicates with ROW_NUMBER()

Orders 105 and 107 are the same order entered twice. Partition by every column that defines a duplicate, and any row numbered 2 or higher is an extra copy:

SELECT id, customer, order_date, amount,
       ROW_NUMBER() OVER (
         PARTITION BY customer, order_date, amount
         ORDER BY id
       ) AS rn
FROM orders
ORDER BY customer, order_date;
id  | customer | order_date | amount | rn
----+----------+------------+--------+---
101 | acme     | 2026-09-02 | 250    | 1
103 | acme     | 2026-10-11 | 400    | 1
106 | acme     | 2026-11-03 | 180    | 1
102 | globex   | 2026-09-05 | 120    | 1
105 | globex   | 2026-10-20 | 310    | 1
107 | globex   | 2026-10-20 | 310    | 2
104 | initech  | 2026-10-15 | 90     | 1
(7 rows)

ORDER BY id decides which copy is the "original" (the lowest id here). To delete the extras, select the ids with rn > 1 and delete those:

DELETE FROM orders
WHERE id IN (
  SELECT id FROM (
    SELECT id,
           ROW_NUMBER() OVER (
             PARTITION BY customer, order_date, amount
             ORDER BY id
           ) AS rn
    FROM orders
  ) d
  WHERE rn > 1
);
-- 1 row deleted (order 107)

Run the SELECT first and check the rows before you delete anything. In SQL Server you can delete straight through a CTE (WITH d AS (...) DELETE FROM d WHERE rn > 1). The remove duplicates guide has the version for each database and shows how to stop duplicates coming back with a unique index.

Pagination with ROW_NUMBER()

Before OFFSET ... FETCH existed, SQL Server and Oracle developers paged through results with ROW_NUMBER(), and you'll still see it in older code. Page 2, with three rows per page:

WITH numbered AS (
  SELECT name, salary,
         ROW_NUMBER() OVER (ORDER BY salary DESC, id) AS rn
  FROM employees
)
SELECT rn, name, salary
FROM numbered
WHERE rn BETWEEN 4 AND 6;
rn | name  | salary
---+-------+-------
4  | Alice | 71000
5  | Maya  | 62000
6  | Sam   | 48000
(3 rows)

Today, LIMIT 3 OFFSET 3 (MySQL, PostgreSQL, SQLite) or OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY (SQL Server 2012+, Oracle 12c+) is shorter and does the same thing. Either way, end the ORDER BY with a unique column so no row shows up on two pages. More in how to limit results in T-SQL.

QUALIFY ROW_NUMBER() in Snowflake, BigQuery and DuckDB

QUALIFY is a filter that runs after window functions, the way HAVING runs after GROUP BY. It removes the need for a subquery:

SELECT name, department, salary
FROM employees
QUALIFY ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, name) = 1
ORDER BY department;
name  | department  | salary
------+-------------+-------
Priya | Engineering | 105000
Alice | Sales       | 71000
Jonas | Support     | 48000

Same result as the subquery version, in one statement. The latest-order-per-customer query becomes:

SELECT customer, id, order_date, amount
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer ORDER BY order_date DESC, id DESC) = 1;

QUALIFY works in Snowflake, BigQuery, Databricks, DuckDB, Teradata and ClickHouse. It does not work in MySQL, PostgreSQL, SQL Server, Oracle or SQLite; use the subquery or CTE there. See the Snowflake QUALIFY docs and BigQuery's QUALIFY clause.

ROW_NUMBER vs RANK vs DENSE_RANK

All three number rows in order. They only differ when rows tie:

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number,
       RANK()       OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;
name  | salary | row_number | rank | dense_rank
------+--------+------------+------+-----------
Priya | 105000 | 1          | 1    | 1
Leo   | 98000  | 2          | 2    | 2
Bella | 98000  | 3          | 2    | 2
Alice | 71000  | 4          | 4    | 3
Maya  | 62000  | 5          | 5    | 4
Sam   | 48000  | 6          | 6    | 5
Jonas | 48000  | 7          | 6    | 5
(7 rows)
FunctionTies getGaps after ties?Use it when
ROW_NUMBER()Different numbers (2, 3)NoYou need exactly one row per position: latest row, deduplication, pagination
RANK()The same number (2, 2)Yes (next is 4)Competition-style ranking where everyone tied for a place is kept
DENSE_RANK()The same number (2, 2)No (next is 3)"Nth highest value" questions, such as the 2nd highest salary

A quick rule: if the question says "the row", use ROW_NUMBER(); if it says "everyone who", use RANK() or DENSE_RANK(). There are separate walkthroughs of RANK() and DENSE_RANK(), and the window functions guide covers NTILE, LAG, LEAD and running totals.

ROW_NUMBER() without ORDER BY

PostgreSQL, MySQL and SQLite let you leave out the ORDER BY:

SELECT name, ROW_NUMBER() OVER () AS rn
FROM employees;
name  | rn
------+---
Maya  | 1
Leo   | 2
Priya | 3
Sam   | 4
Alice | 5
Jonas | 6
Bella | 7
(7 rows)

The rows are numbered in whatever order the database reads them, which happens to be insert order on this tiny table. Don't rely on that. SQL Server refuses to run it at all: The function 'ROW_NUMBER' must have an OVER clause with ORDER BY. If you really want arbitrary numbers there (say, to give every row a unique id in a one-off export), the usual workaround is:

-- SQL Server: number rows in no particular order
SELECT name, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
FROM employees;

ROW_NUMBER() with GROUP BY and WHERE

Window functions run after WHERE, GROUP BY and HAVING, so they number the rows that are left. Filter first, then number:

SELECT ROW_NUMBER() OVER (ORDER BY name) AS rn, name
FROM employees
WHERE department = 'Engineering';
rn | name
---+------
1  | Bella
2  | Leo
3  | Priya
(3 rows)

Only the three engineers are numbered. With GROUP BY, ROW_NUMBER() numbers the groups, and its ORDER BY can use an aggregate:

SELECT department, COUNT(*) AS headcount,
       ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC, department) AS rn
FROM employees
GROUP BY department;
department  | headcount | rn
------------+-----------+---
Engineering | 3         | 1
Sales       | 2         | 2
Support     | 2         | 3
(3 rows)

The full order is FROM, WHERE, GROUP BY, HAVING, window functions, SELECT, DISTINCT, ORDER BY, LIMIT. That's also why SELECT DISTINCT with ROW_NUMBER() rarely removes anything: every row already has a different number by the time DISTINCT runs.

Row numbers without window functions (MySQL 5.7 and older)

MySQL 5.7, SQLite before 3.25 and some embedded databases have no ROW_NUMBER(). A correlated subquery that counts the rows "ahead" of each row works in any database:

SELECT name, salary,
       (SELECT COUNT(*)
        FROM employees e2
        WHERE e2.salary > e.salary
           OR (e2.salary = e.salary AND e2.id <= e.id)) AS row_num
FROM employees e
ORDER BY row_num;
name  | salary | row_num
------+--------+--------
Priya | 105000 | 1
Leo   | 98000  | 2
Bella | 98000  | 3
Alice | 71000  | 4
Maya  | 62000  | 5
Sam   | 48000  | 6
Jonas | 48000  | 7
(7 rows)

It matches ROW_NUMBER() OVER (ORDER BY salary DESC, id), but it reads the table once per row, so it gets slow quickly. Old MySQL answers online use session variables (@rn := @rn + 1) instead. MySQL 8.0 deprecated setting variables inside expressions, and the evaluation order was never guaranteed, so upgrade to MySQL 8 and use ROW_NUMBER() if you can.

ROW_NUMBER() in MySQL, PostgreSQL, SQL Server, Oracle, SQLite and Snowflake

The syntax is the same everywhere. What differs is the minimum version, whether ORDER BY is required, and how you filter on the result:

DatabaseSupported sinceORDER BY required?QUALIFY?
MySQL8.0NoNo
MariaDB10.2NoNo
PostgreSQL8.4NoNo
SQL Server2005YesNo
Oracle8iYesNo
SQLite3.25NoNo
SnowflakeAll versionsYesYes
BigQueryAll versionsNoYes
Databricks (Spark SQL)All versionsYesYes
DuckDBAll versionsNoYes

A named WINDOW clause saves repeating the same OVER (...) when you use several window functions. It works in MySQL 8, PostgreSQL, SQLite, BigQuery, DuckDB, SQL Server 2022+ and Oracle 21c+:

SELECT name, salary,
       ROW_NUMBER() OVER w AS rn,
       SUM(salary)  OVER w AS running_total
FROM employees
WINDOW w AS (ORDER BY salary DESC, id);
name  | salary | rn | running_total
------+--------+----+--------------
Priya | 105000 | 1  | 105000
Leo   | 98000  | 2  | 203000
Bella | 98000  | 3  | 301000
Alice | 71000  | 4  | 372000
Maya  | 62000  | 5  | 434000
Sam   | 48000  | 6  | 482000
Jonas | 48000  | 7  | 530000
(7 rows)

Official references: SQL Server ROW_NUMBER, MySQL window functions, PostgreSQL window functions, SQLite window functions and Snowflake ROW_NUMBER.

ROW_NUMBER() performance

To number rows, the database has to sort them by the PARTITION BY and ORDER BY columns. SQLite's plan for the latest-order query shows the sort:

EXPLAIN QUERY PLAN
SELECT customer, id,
       ROW_NUMBER() OVER (PARTITION BY customer ORDER BY order_date DESC, id DESC) AS rn
FROM orders;

CO-ROUTINE (subquery-2)
  SCAN orders
  USE TEMP B-TREE FOR ORDER BY
SCAN (subquery-2)

An index that matches the window, partition columns first and then the order columns, lets the database read rows already in order and skip the sort:

CREATE INDEX idx_orders_customer_date
  ON orders (customer, order_date DESC, id DESC);

CO-ROUTINE (subquery-2)
  SCAN orders USING COVERING INDEX idx_orders_customer_date
SCAN (subquery-2)

"USE TEMP B-TREE FOR ORDER BY" is gone. On large tables that's the difference between seconds and minutes. Other tips:

  • Filter before numbering. A WHERE clause inside the subquery or CTE shrinks what has to be sorted.
  • Reuse one window. Several functions with the same OVER (...) share one sort.
  • For "top 1 per group" on huge tables, a correlated subquery or LATERAL join with LIMIT 1 can beat ROW_NUMBER() when the groups are few and the index fits. Measure both.

More in how to improve SQL query performance.

Common ROW_NUMBER() mistakes

  1. Filtering in WHERE. Window functions don't exist yet when WHERE runs. Use a subquery, a CTE or QUALIFY. (Example)
  2. No tie-breaker. Tied rows get numbers in an arbitrary order, so "row 1" can change between runs. End the ORDER BY with a unique column. (Example)
  3. Using ROW_NUMBER() when ties should count equally. "Top 3 salaries" with ties at third place needs RANK() or DENSE_RANK(). (Example)
  4. Expecting the OVER (ORDER BY ...) to sort the output. It only controls the numbering. Add an outer ORDER BY. (Example)
  5. Forgetting PARTITION BY. Without it the numbering runs across the whole result, and rn = 1 returns one row total instead of one per group.
  6. Partitioning by too many columns. PARTITION BY customer, order_date numbers each customer-and-date pair, so every customer can have several "row 1"s.
  7. Deleting duplicates without checking first. Run the SELECT ... WHERE rn > 1 and look at the rows before turning it into a DELETE. (Example)

FAQ

What does ROW_NUMBER() do in SQL?

It assigns a unique, sequential integer to each row of a result, starting at 1, in the order given by the ORDER BY inside its OVER clause. With PARTITION BY, the numbering restarts at 1 for each group.

What does ROW_NUMBER() OVER (PARTITION BY ...) mean?

It numbers rows separately for each group. ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) gives the highest earner in each department 1, the next 2, and so on, starting again in the next department.

How do I select only rows where ROW_NUMBER() = 1?

Calculate it in a subquery or CTE and filter in the outer query: WITH r AS (SELECT *, ROW_NUMBER() OVER (...) AS rn FROM t) SELECT * FROM r WHERE rn = 1. In Snowflake, BigQuery, Databricks and DuckDB you can use QUALIFY ROW_NUMBER() OVER (...) = 1 instead.

What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

With tied values, ROW_NUMBER() still gives every row a different number (1, 2, 3), RANK() gives ties the same number and skips the next (1, 2, 2, 4), and DENSE_RANK() gives ties the same number without skipping (1, 2, 2, 3).

Can ROW_NUMBER() be used without ORDER BY?

In PostgreSQL, MySQL, SQLite and BigQuery, yes, but the numbers follow whatever order the database reads rows in, which isn't guaranteed. SQL Server, Oracle, Snowflake and Databricks require ORDER BY; in SQL Server write ORDER BY (SELECT NULL) if the order really doesn't matter.

Does MySQL have ROW_NUMBER()?

Yes, from MySQL 8.0. MySQL 5.7 and older have no window functions; use a correlated subquery or upgrade. See the workaround.

Is ROW_NUMBER() deterministic?

Only if the ORDER BY inside OVER has no ties within a partition. Add a unique column, such as the primary key, at the end of the ORDER BY to make it deterministic.

How do I add a row number column to a query?

Add ROW_NUMBER() OVER (ORDER BY some_column) AS row_num to the SELECT list. It's calculated when the query runs and isn't stored. To store a permanent number, use an auto-increment column instead.

Is ROW_NUMBER() slow?

It needs a sort by the partition and order columns. On large tables, an index on those columns lets the database skip the sort. See the query plan.

Practice ROW_NUMBER()

ROW_NUMBER() builds on ORDER BY and GROUP BY, so make sure those are solid first: the free interactive ORDER BY, GROUP BY and subquery lessons run in your browser with no signup. Then try the "top N per group" and "latest row" problems in SQL practice problems and SQL coding interview questions, where ROW_NUMBER() is usually the cleanest answer. Keep the SQL cheat sheet handy for the syntax.

When you're ready to run window functions on your own data, Beekeeper Studio is a free, open source SQL editor for PostgreSQL, MySQL, SQLite and SQL Server.