SQL ROW_NUMBER(): Syntax, PARTITION BY & Top-N Examples

ROW_NUMBER() adds a column of row numbers without removing or merging any rows. The ORDER BY inside OVER (...) decides which row is number 1. Here the highest salary gets 1:

SELECT first_name, last_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, id) AS row_num
FROM employees
ORDER BY row_num
LIMIT 5;

▶ Run this query in the SQL Playground

ROW_NUMBER() OVER (
  [PARTITION BY group_column]
  ORDER BY sort_column
)

ROW_NUMBER() is one of the SQL window functions. It is available in PostgreSQL, SQL Server, Oracle, MySQL 8.0 and later, and SQLite 3.25 and later.

PARTITION BY department splits the rows into one group per department and numbers each group separately. Two departments are shown so you can see the count restart:

SELECT department, first_name, last_name, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, id) AS row_num
FROM employees
WHERE department IN ('Design', 'Product')
ORDER BY department, row_num
LIMIT 12;

▶ Run this query in the SQL Playground

The first Design employee is 1, and so is the first Product employee. Unlike GROUP BY, the rows are not collapsed: every employee is still there.

A window function cannot be used in WHERE, because WHERE runs before the numbers are calculated. Compute the row number in a CTE first, then filter on it. This returns the two best-paid employees in every department:

WITH ranked AS (
  SELECT department, first_name, last_name, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, id) AS rn
  FROM employees
)
SELECT department, first_name, last_name, salary
FROM ranked
WHERE rn <= 2
ORDER BY department, salary DESC
LIMIT 8;

▶ Run this query in the SQL Playground

Change rn <= 2 to rn = 1 for only the top row of each group, or to any other number for a different N.

The same pattern picks one row per group when a table holds several. Number each customer's orders from newest to oldest and keep number 1:

WITH numbered AS (
  SELECT user_id, id AS order_id, order_date, total,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC, id DESC) AS rn
  FROM orders
)
SELECT user_id, order_id, order_date, total
FROM numbered
WHERE rn = 1
ORDER BY user_id
LIMIT 5;

▶ Run this query in the SQL Playground

This is also the standard way to remove duplicates: partition by the columns that define a duplicate, order by whichever copy you want to keep, and keep rn = 1.

ROW_NUMBER() never repeats a number. If two rows have the same value in the ORDER BY column, the database still has to put one first, and without further instructions it may choose differently each time. Add a unique column, such as id, as a tie-breaker so the result is the same on every run. All examples on this page do that.

Three errors and how to fix them.

SELECT first_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
WHERE rn <= 3;

WITH numbered AS (
  SELECT first_name, salary,
         ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT first_name, salary
FROM numbered
WHERE rn <= 3;

ROW_NUMBER() OVER (PARTITION BY department)

ROW_NUMBER() OVER (
  PARTITION BY department
  ORDER BY salary DESC, id
)

ROW_NUMBER() OVER (ORDER BY score DESC) AS rn
-- then WHERE rn <= 3

DENSE_RANK() OVER (ORDER BY score DESC) AS rnk
-- then WHERE rnk <= 3

4 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy Number all products from the most expensive to the cheapest and show the first five. Table: products

   Show solution

   SQLite · runs in the Playground

   SELECT name, price,
          ROW_NUMBER() OVER (ORDER BY price DESC, id) AS row_num
   FROM products
   ORDER BY row_num
   LIMIT 5;

   ▶ Run this query in the SQL Playground

   ORDER BY price DESC inside OVER makes the most expensive product number 1; id breaks ties.

2. 2Medium Return the most expensive product in each category. Table: products

   Show solution

   SQLite · runs in the Playground

   WITH ranked AS (
     SELECT category, name, price,
            ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC, id) AS rn
     FROM products
   )
   SELECT category, name, price
   FROM ranked
   WHERE rn = 1
   ORDER BY category;

   ▶ Run this query in the SQL Playground

   One row per category, 10 in total: the row numbered 1 in each partition.

3. 3Medium Show each customer's first order (the earliest order date). Return five rows. Table: orders

   Show solution

   SQLite · runs in the Playground

   WITH numbered AS (
     SELECT user_id, id AS order_id, order_date,
            ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date, id) AS rn
     FROM orders
   )
   SELECT user_id, order_id, order_date
   FROM numbered
   WHERE rn = 1
   ORDER BY user_id
   LIMIT 5;

   ▶ Run this query in the SQL Playground

   Ordering by order_date ascending makes the earliest order number 1 for each customer.

4. 4Challenging Return the third to fifth highest-paid employees in the whole company (a simple form of pagination). Table: employees

   Show solution

   SQLite · runs in the Playground

   WITH numbered AS (
     SELECT first_name, last_name, salary,
            ROW_NUMBER() OVER (ORDER BY salary DESC, id) AS rn
     FROM employees
   )
   SELECT rn, first_name, last_name, salary
   FROM numbered
   WHERE rn BETWEEN 3 AND 5
   ORDER BY rn;

   ▶ Run this query in the SQL Playground

   Filtering on a range of row numbers returns one "page" of 3 rows.