A shop wants to compare its salespeople. Ada and Ben sold the same amount, so giving one second place and the other third place may be unfair. A different report needs exactly one row per salesperson, even when amounts tie. SQL provides three ranking functions because those are different questions.
ROW_NUMBER() gives each row a distinct position. RANK() gives tied rows the same rank and leaves gaps afterward. DENSE_RANK() gives tied rows the same rank without gaps. All three are window functions: they add a result to each row rather than collapsing several rows into one summary. Start with the original window functions lesson if OVER is new to you.
How ranking works
The ORDER BY inside OVER decides which values rank first. For a sales leaderboard, descending amounts put the highest total first. For a race time, ascending durations put the quickest competitor first. Neither choice automatically sorts the displayed report; use a final ORDER BY for that.
Ties are defined by the entire ordering expression. With RANK() OVER (ORDER BY amount DESC), equal amounts tie. Adding an employee ID to that same window order makes otherwise equal amounts different ordering values, removing the tie. This is often useful for row numbering but usually wrong when the business wants equal sales to share a rank.
Think of amounts 120, 90, 90 and 60. Row numbers are 1, 2, 3 and 4. Ranks are 1, 2, 2 and 4. Dense ranks are 1, 2, 2 and 3. The gap in ordinary rank records that three people stand ahead of fourth place, even though two share second place.
Top results within each group
Add PARTITION BY when numbering should restart for every department, customer or month. A department leaderboard partitions by department and orders each department's rows by sales. Omitting the partition instead creates one company-wide competition. Decide which report the reader needs before choosing the syntax.
To keep the top rows, calculate the rank in a CTE or derived table and filter it in the outer query. A window result generally is not available to the same query's WHERE clause. Some other databases provide QUALIFY, but it is not a portable replacement across the four systems used in this course.
WITH ranked AS (
SELECT employee_id, employee_name, amount,
DENSE_RANK() OVER (ORDER BY amount DESC) AS r
FROM leaderboard
)
SELECT employee_name, amount
FROM ranked
WHERE r <= 2
ORDER BY amount DESC, employee_id;This returns Ada, Ben and Cara. “Two highest sales levels” includes three employees here. If the requirement says “exactly two employees,” use deterministic ROW_NUMBER() and accept that the tie needs a documented selection policy.
Common mistakes and performance
Using ROW_NUMBER() to deduplicate records is safe only when the window order expresses which record to keep. An arbitrary position can discard the newest or most complete version. Prefer an order such as updated time descending followed by a unique ID, and inspect the selected survivors before any deletion.
Ranking functions do not need a running aggregate frame. A ROWS frame is useful for totals, but it does not turn ranking into a calculation over the previous three records. Use the ranking function's partition and ordering rules for ranks, and learn frame behavior separately.
Large partitions may require substantial sorting. An index matching the partition and ordering columns can help, but the optimizer may still choose another plan. Check execution plans on realistic data rather than assuming a leaderboard that is quick on four rows will remain quick on four million. Retrieve only the columns needed by the report.
Missing sales values also need a policy. Default NULL placement differs between databases. Filtering unknown amounts or adding an explicit NULL ordering expression is clearer than allowing an unknown value to become the apparent winner.
Practice: explain the boundary
Change Ada's amount to 90 so the first three employees tie. Predict each ranking column before running the query. All three sales ranks become 1, Dan becomes rank 4, and his dense rank becomes 2. Row numbers stay distinct because employee ID resolves the order among the three leaders.
Now compare WHERE r <= 2 using ordinary rank and dense rank. Ordinary rank returns only the tied leaders; dense rank also returns Dan because there are only two distinct amount levels. Neither result is automatically wrong. Explain whether the requested boundary concerns competition places, distinct sales levels or a fixed number of people.
For a real report, put that decision in the report definition. A phrase such as “top three” is ambiguous when several rows share third place. Agree on whether ties expand the result before a dashboard depends on it.
Further reading
For syntax details, consult the PostgreSQL window function reference. Its ranking definitions help distinguish the functions without changing the tie policy in your own report.
SQL ROW_NUMBER, RANK and DENSE_RANK Examples
Each example shows the code, the output it produces and what each part does.
Compare the three functions
This self-contained example uses syntax supported by PostgreSQL, MySQL 8+, SQL Server and SQLite versions with window support. Run it in a disposable practice database.
Notice that only row numbering uses the employee ID as a tie-breaker. The two rank columns preserve the tie between Ben and Cara. The final ordering displays Ben before Cara without changing their shared rank.
CREATE TABLE leaderboard (
employee_id INTEGER PRIMARY KEY,
employee_name VARCHAR(30) NOT NULL,
amount INTEGER NOT NULL
);
INSERT INTO leaderboard VALUES
(1, 'Ada', 120), (2, 'Ben', 90),
(3, 'Cara', 90), (4, 'Dan', 60);
SELECT employee_name, amount,
ROW_NUMBER() OVER (ORDER BY amount DESC, employee_id) AS position,
RANK() OVER (ORDER BY amount DESC) AS sales_rank,
DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_sales_rank
FROM leaderboard
ORDER BY amount DESC, employee_id;employee_name | amount | position | sales_rank | dense_sales_rank
Ada | 120 | 1 | 1 | 1
Ben | 90 | 2 | 2 | 2
Cara | 90 | 3 | 2 | 2
Dan | 60 | 4 | 4 | 3SQL ROW_NUMBER, RANK and DENSE_RANK FAQ
Can ROW_NUMBER return the same number twice?
Within one partition it assigns distinct numbers. Different partitions restart numbering, so position 1 may appear for several departments.
Does RANK skip numbers?
Yes, after ties. If two rows share rank 2, the following row is rank 4. Dense rank instead moves to 3.
Why did row numbers change between runs?
If the window order does not uniquely order rows, tied rows can be processed differently. Add a stable unique tie-breaker when distinct positions must be reproducible.
What is the difference between RANK and DENSE_RANK?
Both give tied rows the same number. RANK then skips numbers, so ranks run 1, 2, 2, 4, while DENSE_RANK continues without gaps, 1, 2, 2, 3. Use DENSE_RANK when you want "the top three price levels" and RANK when position in a competition matters.
What does NTILE do?
NTILE(4) OVER (ORDER BY amount) divides the ordered rows into four groups of nearly equal size and returns the group number, 1 to 4. It's useful for quartiles and splitting customers into bands. When rows don't divide evenly, the first groups get one extra row.
SQL ROW_NUMBER, RANK and DENSE_RANK Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Your report must return exactly one latest record per customer. Which approach gives a dependable choice?
SQL ROW_NUMBER, RANK and DENSE_RANK Quiz
2 questions to check what you learned.
For values 100, 80, 80 and 50 ordered descending, what does RANK return?
- 1, 2, 2, 4
- 1, 2, 3, 4
- 1, 2, 2, 3
What happens if employee_id is added to a RANK window order where every employee ID is unique?
- All employees get rank 1
- Equal sales amounts no longer tie under that complete ordering
- It changes display order without affecting the rank calculation