Skip to content
Tutorials Center
My learning

Topics

Lesson 50 of 125 ยท Window Functions

ROW_NUMBER, RANK and DENSE_RANK

Learn ROW_NUMBER, RANK, DENSE_RANK and NTILE in SQL. Handle ties correctly, find the top N rows per group, remove duplicates and split rows into buckets.

"Who are the three best-paid people in each department?" "What is each customer's most recent order?" "Which products are in the top quarter by price?" Questions like these need ranking: numbering rows in a chosen order, often separately for each group.

SQL has four ranking window functions: ROW_NUMBER, RANK, DENSE_RANK and NTILE. They differ in one important detail, how they treat ties, and picking the wrong one is a classic source of subtle bugs. This lesson shows the differences side by side, then uses them for the most common real-world pattern: top N per group.

Three ways to number rows

All ranking functions take their order from the ORDER BY inside OVER, and none of them takes an argument (except NTILE). Run this and look closely at the rows with equal salaries:

SQL ROW_NUMBER vs RANK vs DENSE_RANK ยท database: company

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

Three people earn 93,000. Here is how each function handles them:

salary ROW_NUMBER RANK DENSE_RANK
94,000 9 9 9
93,000 10 10 10
93,000 11 10 10
93,000 12 10 10
89,000 13 13 11
  • ROW_NUMBER() gives every row a unique, consecutive number. Tied rows get different numbers, in an order the database picks.
  • RANK() gives tied rows the same rank, then skips numbers: after three people at rank 10, the next person is 13. This is "sports ranking": if three runners tie for 10th, nobody finishes 11th or 12th.
  • DENSE_RANK() gives tied rows the same rank and doesn't skip: the next person is 11. The highest dense rank tells you how many distinct values there are.

ROW_NUMBER is not deterministic with ties. The three 93,000 earners can come out as 10, 11, 12 in any order, and that order can change between runs, versions or databases. If the numbering matters, add a tie-breaker that makes the order unique, such as ORDER BY salary DESC, id.

Ranking within groups

Add PARTITION BY and the numbering restarts for every group. This ranks employees by salary inside each department:

SQL Rank inside each department ยท database: company

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

Every department starts again at 1 with its head. You can partition by any expression, for example PARTITION BY strftime('%Y', hire_date) to rank people within their hiring year.

Top N per group

Now the payoff. To keep only the two best-paid people per department, compute the rank in a CTE and filter outside it. (Remember: window functions can't appear in WHERE.)

SQL Top 2 earners per department ยท database: company

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

That's 12 rows, two for each of the six departments. Which function you use here is a business decision:

You want Use With ties at the cut-off
Exactly N rows per group ROW_NUMBER() + a tie-breaker Some tied rows are left out
Everyone who ranks in the top N positions RANK() You may get more than N rows
Everyone with one of the N highest values DENSE_RANK() You may get many more than N rows

The same pattern finds "the latest row per group", one of the most common queries in real applications. Here is each customer's most recent order:

SQL Latest order per customer ยท database: shop

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

Ordering by ordered_at DESC makes the newest order number 1. The same trick gives "the current price from a price history", "the last login per user" and "the newest version of each document".

Finding the Nth highest value

"Find the second-highest salary" is a famous interview question. DENSE_RANK answers it cleanly and handles ties the way people usually mean ("the second-highest distinct salary"):

SQL The Nth highest salary ยท database: company

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

Level 10 is 93,000, shared by three people. With ROW_NUMBER you would get whichever 93,000 row happened to be tenth; with RANK, level = 11 would return nothing, because ranks 11 and 12 are skipped. Change the 10 to 2 to get the classic answer: 157,000.

Removing duplicates with ROW_NUMBER

ROW_NUMBER is the standard tool for deduplication: number the copies within each group of duplicates, keep number 1, and delete or ignore the rest. This example builds a small sign-up table where some people registered twice:

SQL Keep the first copy, delete the rest

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

The DELETE removes 3 rows: two extra copies for Ana and one for Ben. Each email keeps its earliest sign-up. Before running a delete like this on real data, run the inner SELECT alone to see exactly which rows it will remove.

NTILE: splitting rows into buckets

NTILE(n) divides the ordered rows into n groups of (almost) equal size and returns the group number, from 1 to n. It's how you compute quartiles, deciles or "top 10%" segments:

SQL Price quartiles with NTILE ยท database: shop

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

There are 26 products, which doesn't divide evenly by 4. NTILE handles that by making the first buckets one row larger: quartiles 1 and 2 get 7 products each, quartiles 3 and 4 get 6 each.

Two things to know about NTILE:

  • It only looks at positions, not values. Two products with the same price can land in different buckets if the boundary falls between them.
  • Buckets are based on row counts, not on value ranges. For "products in the most expensive 25% by price value" or true percentiles, see Percentiles, Medians and Statistics.

Ranking aggregated results

Because window functions run after GROUP BY, you can rank totals directly, in the same query that computes them. This finds the best-selling product in each category by units sold (ignoring cancelled orders):

SQL Best seller per category ยท database: shop

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

ORDER BY SUM(oi.quantity) DESC inside OVER ranks each product by its group total. Using RANK() rather than ROW_NUMBER() here is deliberate: if two products in a category sell the same number of units, both are shown as joint best sellers instead of one being dropped at random.

Ranking and NULL

Ranking functions sort NULLs the same way a query's ORDER BY does, and databases disagree about that: SQLite, MySQL and SQL Server treat NULL as smaller than every value (first in ascending order, last in descending), while PostgreSQL and Oracle treat it as larger. If some rows have no value, say where they go explicitly with NULLS LAST (SQLite 3.30+, PostgreSQL, Oracle) or filter them out, as the percentile examples in this chapter do with grade IS NOT NULL. See Sorting with ORDER BY.

Ranking in ORDER BY and other tricks

Window functions are allowed in the final ORDER BY, and ranks can feed calculations. A few patterns worth knowing:

  • Alternate grouping: ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY id) % 2 splits each department into two alternating teams.
  • Pagination: ROW_NUMBER() OVER (ORDER BY id) between 21 and 30 gives page 3 of 10 rows, the way pagination was done before LIMIT/OFFSET and FETCH FIRST were common. See LIMIT, OFFSET and Pagination.
  • Sorting by rank: ORDER BY DENSE_RANK() OVER (...) works, but naming the rank in the SELECT list and sorting by its alias is clearer.

Dialect notes

The four functions are standard and identical in SQLite, PostgreSQL, MySQL 8, SQL Server and Oracle. Two dialect features are worth recognizing in other people's code:

SQL
-- Snowflake, BigQuery, DuckDB, Databricks: QUALIFY filters on window results
-- without a CTE (not available in SQLite, PostgreSQL, MySQL or SQL Server)
SELECT department_id, first_name, salary
FROM employees
QUALIFY ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) <= 2;

-- SQL Server: TOP ... WITH TIES keeps every row tied with the last one
SELECT TOP 10 WITH TIES first_name, salary
FROM employees
ORDER BY salary DESC;

The standard equivalent of WITH TIES is FETCH FIRST 10 ROWS WITH TIES (PostgreSQL 13+, Oracle 12c+). In SQLite, use RANK() <= 10 in a CTE, which gives the same result.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Salaries are 100, 90, 90, 80 (sorted descending). What does RANK() give the 80 row?

    The two 90s share rank 2, and RANK skips the next number, so 80 is ranked 4. DENSE_RANK would give 3.
  2. 2. You need exactly one row per customer (their latest order). Which function fits best?

    ROW_NUMBER never repeats a number, so = 1 always picks one row. RANK or DENSE_RANK could return two orders placed at the same moment.
  3. 3. Why does the top-N-per-group query compute the rank in a CTE?

    Window functions are evaluated after WHERE. Ranking inside a CTE (or subquery) and filtering outside is the standard pattern.
  4. 4. What does DENSE_RANK() OVER (ORDER BY price DESC) return for the cheapest product if there are 26 products but only 25 distinct prices?

    DENSE_RANK assigns consecutive numbers to distinct values, so the cheapest of 25 distinct prices gets 25.
  5. 5. NTILE(4) is applied to 10 rows. How big are the buckets?

    The rows are spread as evenly as possible, and the extra rows go to the first buckets.

Summary

  • ROW_NUMBER() numbers rows uniquely; add a tie-breaker to make it deterministic.
  • RANK() gives ties the same rank and skips numbers after them; DENSE_RANK() doesn't skip.
  • PARTITION BY restarts the numbering in each group.
  • Top N per group: rank inside a CTE, then filter with WHERE rank_column <= N.
  • ROW_NUMBER() ... = 1 finds the latest or first row per group and drives deduplication.
  • NTILE(n) splits ordered rows into n nearly equal buckets, with larger buckets first.

Next: Running Totals and Moving Averages, where window aggregates accumulate values row by row.