Lesson 19.1

Ranking and Ties in SQL

ROW_NUMBER, RANK, and DENSE_RANK agree until two rows tie. Then each follows a different rule, and picking the wrong one changes the answer.

Lesson 11.4 · Window functions numbered rows with row_number(). Two other functions number rows too, and the three give identical answers until two rows tie. Then each follows its own rule, and the rule you pick changes the result.

Three rules for a tie

row_number() gives every row a different number. Tied rows are numbered in the order of the window's ORDER BY, so without a tie-breaker the order among them is up to the engine.
rank() gives tied rows the same number, then skips: 1, 2, 2, 4. It's how a race ranks a tie for second.
dense_rank() gives tied rows the same number and doesn't skip: 1, 2, 2, 3. The numbers count distinct values, not rows.

Pattern

select player,
       score,
       row_number() over (order by score desc, player) as row_num,
       rank()       over (order by score desc)         as score_rank,
       dense_rank() over (order by score desc)         as dense_score_rank
from results

Ranking grouped rows

Windows run after GROUP BY, so a window can order by an aggregate. In this lesson count(*) is each customer's order count, and rank() over (order by count(*) desc) ranks the customers by it in the same query.

Which one to use

Use row_number() when you need exactly N rows, such as one row per customer or the top 3 per category, and choose the tie-breaker on purpose. Use rank() when ties should share a place and the places after them should reflect how many rows came first. Use dense_rank() for "the second-highest value", where you mean the second distinct value. Filtering on rank() = 1 to get one row per group is a common bug; see why rank() = 1 returns more than one row.

A fourth function, ntile(n), splits the ordered rows into n groups as evenly as it can, which is how quartiles and deciles are usually built.

Schema · Garden Shop Tables · customers, orders 2 tables

Table · customers

One row per customer. Some customers have no phone on file.

9 columns · 20 rows

customer_id int first_name text last_name text email text phone text city text state text signup_date date is_active bool

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Syntax reference

Need syntax details? See the SQL window function reference

Your task

Rank the customers who have placed orders by how many orders they placed. Return customer_id, first_name, order_count, and three rankings of order_count from most to fewest: row_num from row_number() (break ties by customer_id), order_rank from rank(), and dense_order_rank from dense_rank(). Sort by order_count descending, then customer_id.

Garden Shop · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

Expected answer

Columns: customer_id, first_name, order_count, row_num, order_rank, dense_order_rank.
Rows: the 16 customers who have ordered, busiest first, ties by customer_id.
Six customers tie on 2 orders and all get order_rank 2. The next customer gets 8 from rank() and 3 from dense_rank().

← Back to all lessons

Next · 19.2 LAG and LEAD →

Reference

SQL Window Functions

OVER, PARTITION BY, ranking, offsets, and running totals.

Cheatsheet

SQL Window Functions Cheatsheet

Ranking, LAG/LEAD, and running totals at a glance.

Interview

SQL Ranking and Top-N Interview Questions

Practice rank functions, tie handling, and top-N per group.

Mistake