"Find the top 3 earners." "Find the second-highest salary." "Show the latest order of every customer." "Find the top 2 employees in each department." These are some of the most common SQL interview questions, and all of them become easy once you know the three ranking functions: ROW_NUMBER(), RANK() and DENSE_RANK().
In the Window Functions chapter we learned how OVER (PARTITION BY ... ORDER BY ...) lets a function look at a group of rows without collapsing them. In this chapter we focus on the window functions that give each row a position number, and most importantly on how they behave when there are ties. We will also look at NTILE(), PERCENT_RANK() and CUME_DIST().
All examples use the shared company_db sample database, with real MySQL 8.4 output.
The Three Ranking Functions at a Glance
All three functions number the rows of a window in the order given by the window's ORDER BY. They differ only in what they do with rows that have the same sort value:
Function | What tied rows get | Gaps after a tie? | Example for values 100, 90, 90, 80 |
|---|---|---|---|
| Different numbers (ties are broken arbitrarily) | No | 1, 2, 3, 4 |
| The same number | Yes | 1, 2, 2, 4 |
| The same number | No | 1, 2, 2, 3 |
A good way to remember it is to think of a race:
ROW_NUMBER is like a queue: everyone gets a unique ticket number, even if two people arrived together.
RANK is like an Olympic result: two runners tie for silver, so nobody gets bronze. The next runner is 4th, because three people finished ahead of them.
DENSE_RANK counts distinct results: two silvers, and the next runner gets the next medal, bronze (3rd).
The general syntax is the same for all three. The parentheses after the function name are always empty; the work is done by the OVER clause.
ROW_NUMBER() OVER ([PARTITION BY col, ...] ORDER BY col [ASC|DESC], ...)
RANK() OVER ([PARTITION BY col, ...] ORDER BY col [ASC|DESC], ...)
DENSE_RANK() OVER ([PARTITION BY col, ...] ORDER BY col [ASC|DESC], ...)ROW_NUMBER(): A Unique Sequence Number
ROW_NUMBER() numbers rows 1, 2, 3, ... in the window order. Let us number employees from the highest salary to the lowest:
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;+--------------+-----------+---------+
| name | salary | row_num |
+--------------+-----------+---------+
| Aarav Sharma | 150000.00 | 1 |
| Sneha Kapoor | 110000.00 | 2 |
| Priya Iyer | 95000.00 | 3 |
| Rohan Mehta | 95000.00 | 4 |
| Arjun Reddy | 82000.00 | 5 |
| Karan Patel | 70000.00 | 6 |
| Vikram Singh | 62000.00 | 7 |
| Ananya Das | 58000.00 | 8 |
| Ishita Gupta | 55000.00 | 9 |
| Meera Nair | 48000.00 | 10 |
+--------------+-----------+---------+
10 rows in set (0.00 sec)Look at Priya and Rohan. Both earn 95,000, yet Priya got 3 and Rohan got 4. Why Priya first? There is no reason. The ORDER BY salary DESC does not say how to order them, so MySQL picks whatever order it happens to read them in. On another day (after an index change, more data, or an upgrade) Rohan could get 3.
ROW_NUMBER() is deterministic only if the window's ORDER BY is unique. When ties are possible, add a tie-breaker column, usually the primary key.
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC, emp_id) AS row_num
FROM employees;Now the result is guaranteed: among equal salaries the lower emp_id comes first, so Priya (emp_id 2) always gets 3 and Rohan (emp_id 3) always gets 4. The output looks the same as above, but this time it is a promise, not luck.
RANK and DENSE_RANK Side by Side
The clearest way to see the difference is to compute all three in one query:
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;+--------------+-----------+---------+-----+-----------+
| name | salary | row_num | rnk | dense_rnk |
+--------------+-----------+---------+-----+-----------+
| Aarav Sharma | 150000.00 | 1 | 1 | 1 |
| Sneha Kapoor | 110000.00 | 2 | 2 | 2 |
| Priya Iyer | 95000.00 | 3 | 3 | 3 |
| Rohan Mehta | 95000.00 | 4 | 3 | 3 |
| Arjun Reddy | 82000.00 | 5 | 5 | 4 |
| Karan Patel | 70000.00 | 6 | 6 | 5 |
| Vikram Singh | 62000.00 | 7 | 7 | 6 |
| Ananya Das | 58000.00 | 8 | 8 | 7 |
| Ishita Gupta | 55000.00 | 9 | 9 | 8 |
| Meera Nair | 48000.00 | 10 | 10 | 9 |
+--------------+-----------+---------+-----+-----------+
10 rows in set (0.00 sec)Let us trace the interesting part, rows 3 to 5:
Row Salary RANK rule RANK DENSE_RANK rule DENSE_RANK
--- ------- ----------------------------------------- ---- ----------------------------------- ----------
3 95000 2 rows have a higher salary -> 2 + 1 3 2 distinct higher salaries -> 2 + 1 3
4 95000 still 2 rows higher -> 3 (tie) 3 still 2 distinct higher -> 3 3
5 82000 4 rows have a higher salary -> 4 + 1 5 3 distinct higher salaries -> 3 + 1 4This gives us exact definitions you can say in an interview:
RANK = 1 + (number of rows whose sort value comes strictly before this row's value; tied rows do not count). So after a tie it "skips" numbers. The last rank can be as large as the row count.
DENSE_RANK = 1 + (number of distinct values that come strictly before this row). It never skips. The last dense rank equals the number of distinct values (here 9, because 10 salaries contain one duplicate).
Notice that the rows themselves are identical in all three columns; only the numbering changes. Ranking functions never filter or merge rows.
Ranking Within Groups: PARTITION BY
Add PARTITION BY and the numbering restarts at 1 for each group. Here every employee is ranked inside their own department:
SELECT name, dept_id, salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_in_dept
FROM employees
ORDER BY dept_id, rank_in_dept;+--------------+---------+-----------+--------------+
| name | dept_id | salary | rank_in_dept |
+--------------+---------+-----------+--------------+
| Ishita Gupta | NULL | 55000.00 | 1 |
| Aarav Sharma | 10 | 150000.00 | 1 |
| Priya Iyer | 10 | 95000.00 | 2 |
| Rohan Mehta | 10 | 95000.00 | 2 |
| Sneha Kapoor | 20 | 110000.00 | 1 |
| Vikram Singh | 20 | 62000.00 | 2 |
| Ananya Das | 20 | 58000.00 | 3 |
| Karan Patel | 30 | 70000.00 | 1 |
| Meera Nair | 30 | 48000.00 | 2 |
| Arjun Reddy | 40 | 82000.00 | 1 |
+--------------+---------+-----------+--------------+
10 rows in set (0.00 sec)Two things to notice:
Ishita has no department (
dept_idis NULL). For partitioning, all NULLs are put in one group together, just like in GROUP BY, so she is ranked 1 in the "NULL department".The final
ORDER BY dept_id, rank_in_deptis the query's own ORDER BY. It controls how the result is displayed. TheORDER BYinsideOVER()only controls the numbering. They are independent, and you usually want both.
The WINDOW Clause
When several functions use the same window, repeating it is noisy and error-prone. MySQL 8 lets you name a window once with the WINDOW clause (placed after HAVING, before ORDER BY):
SELECT name, dept_id, salary,
ROW_NUMBER() OVER w AS rn,
RANK() OVER w AS rnk,
DENSE_RANK() OVER w AS dr
FROM employees
WINDOW w AS (PARTITION BY dept_id ORDER BY salary DESC);+--------------+---------+-----------+----+-----+----+
| name | dept_id | salary | rn | rnk | dr |
+--------------+---------+-----------+----+-----+----+
| Ishita Gupta | NULL | 55000.00 | 1 | 1 | 1 |
| Aarav Sharma | 10 | 150000.00 | 1 | 1 | 1 |
| Priya Iyer | 10 | 95000.00 | 2 | 2 | 2 |
| Rohan Mehta | 10 | 95000.00 | 3 | 2 | 2 |
| Sneha Kapoor | 20 | 110000.00 | 1 | 1 | 1 |
| Vikram Singh | 20 | 62000.00 | 2 | 2 | 2 |
| Ananya Das | 20 | 58000.00 | 3 | 3 | 3 |
| Karan Patel | 30 | 70000.00 | 1 | 1 | 1 |
| Meera Nair | 30 | 48000.00 | 2 | 2 | 2 |
| Arjun Reddy | 40 | 82000.00 | 1 | 1 | 1 |
+--------------+---------+-----------+----+-----+----+
10 rows in set (0.00 sec)Inside Engineering (dept 10) the tie appears again: ROW_NUMBER gives 2 and 3, while RANK and DENSE_RANK both give 2. In Sales (dept 20) there are no ties, so all three columns agree. The three functions only differ when there are ties.
Filtering on a Rank: Why You Need a CTE or Subquery
The natural first attempt at "top 3 earners" fails:
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
WHERE rnk <= 3;ERROR 1054 (42S22): Unknown column 'rnk' in 'where clause'Putting the function itself in WHERE fails too:
SELECT name, salary FROM employees
WHERE RANK() OVER (ORDER BY salary DESC) <= 3;ERROR 3593 (HY000): You cannot use the window function 'rank' in this context.'The reason is the logical order of evaluation:
FROM -> WHERE -> GROUP BY -> HAVING -> window functions -> SELECT list -> ORDER BY -> LIMITWindow functions are computed after WHERE, GROUP BY and HAVING have finished. While WHERE is running, no rank exists yet. (This is also why HAVING cannot filter on a window function.)
The fix is to compute the rank in an inner query and filter in an outer query. A CTE (from the Common Table Expressions chapter) is the cleanest way:
WITH ranked AS (
SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
FROM employees
)
SELECT name, salary
FROM ranked
WHERE dr = 3;+-------------+----------+
| name | salary |
+-------------+----------+
| Priya Iyer | 95000.00 |
| Rohan Mehta | 95000.00 |
+-------------+----------+
2 rows in set (0.00 sec)This is the modern answer to the classic "Nth highest salary" question. The third-highest distinct salary is 95,000, and both people who earn it are returned. Change dr = 3 to dr = N for any N.
Which function should you use for "Nth highest salary"? It depends on what the question means:
Meaning of "3rd highest" | Use | Result here |
|---|---|---|
3rd highest distinct salary value (the usual meaning) |
| 95,000 (Priya and Rohan) |
Person in 3rd position, with ties sharing it |
| Priya and Rohan; note that |
Exactly one row in 3rd position |
| Priya only |
In an interview, it is worth saying this out loud: "If salaries can repeat, I'd use DENSE_RANK so that duplicates don't shift the answer."
Top N Per Group
"Top 2 earners in each department" combines PARTITION BY with a filter. Here ROW_NUMBER and DENSE_RANK give different answers, so let us compute both and filter on DENSE_RANK:
WITH ranked AS (
SELECT name, dept_id, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dr
FROM employees
WHERE dept_id IS NOT NULL
)
SELECT name, dept_id, salary, rn, dr
FROM ranked
WHERE dr <= 2
ORDER BY dept_id, dr, name;+--------------+---------+-----------+----+----+
| name | dept_id | salary | rn | dr |
+--------------+---------+-----------+----+----+
| Aarav Sharma | 10 | 150000.00 | 1 | 1 |
| Priya Iyer | 10 | 95000.00 | 2 | 2 |
| Rohan Mehta | 10 | 95000.00 | 3 | 2 |
| Sneha Kapoor | 20 | 110000.00 | 1 | 1 |
| Vikram Singh | 20 | 62000.00 | 2 | 2 |
| Karan Patel | 30 | 70000.00 | 1 | 1 |
| Meera Nair | 30 | 48000.00 | 2 | 2 |
| Arjun Reddy | 40 | 82000.00 | 1 | 1 |
+--------------+---------+-----------+----+----+
8 rows in set (0.00 sec)Engineering returns three people, because Priya and Rohan tie for second. If you filter on rn <= 2 instead, you get exactly two per department and one of the tied employees is silently dropped:
WITH ranked AS (
SELECT name, dept_id, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM employees
WHERE dept_id IS NOT NULL
)
SELECT name, dept_id, salary
FROM ranked
WHERE rn <= 2
ORDER BY dept_id, rn;+--------------+---------+-----------+
| name | dept_id | salary |
+--------------+---------+-----------+
| Aarav Sharma | 10 | 150000.00 |
| Priya Iyer | 10 | 95000.00 |
| Sneha Kapoor | 20 | 110000.00 |
| Vikram Singh | 20 | 62000.00 |
| Karan Patel | 30 | 70000.00 |
| Meera Nair | 30 | 48000.00 |
| Arjun Reddy | 40 | 82000.00 |
+--------------+---------+-----------+
7 rows in set (0.00 sec)Neither is "wrong"; they answer different questions. Use ROW_NUMBER when you need exactly N rows per group (for example, showing 2 cards per category on a web page), and DENSE_RANK or RANK when ties must be treated fairly (for example, awarding prizes).
Latest Row Per Group
A very practical variant is "the most recent order of every customer". Sort each customer's orders newest first and keep row number 1:
WITH latest AS (
SELECT order_id, customer_id, order_date, amount,
ROW_NUMBER() OVER (PARTITION BY customer_id
ORDER BY order_date DESC) AS rn
FROM orders
WHERE customer_id IS NOT NULL
)
SELECT order_id, customer_id, order_date, amount
FROM latest
WHERE rn = 1;+----------+-------------+------------+---------+
| order_id | customer_id | order_date | amount |
+----------+-------------+------------+---------+
| 1007 | 101 | 2024-03-28 | 5600.00 |
| 1006 | 102 | 2024-03-11 | 3100.00 |
| 1004 | 103 | 2024-02-14 | 7300.00 |
| 1005 | 104 | 2024-03-01 | 950.00 |
+----------+-------------+------------+---------+
4 rows in set (0.00 sec)Before window functions, this needed a correlated subquery or a self-join on MAX(order_date). The ROW_NUMBER version is shorter, and it returns the whole row (amount, status, anything) instead of just the maximum date. If a customer could have two orders on the same date, add order_id DESC as a tie-breaker.
Ranking Aggregated Results
Because window functions run after GROUP BY, you can rank the groups themselves, and even use an aggregate inside the window's ORDER BY. Rank customers by how much they spent, ignoring cancelled orders:
SELECT customer_id,
SUM(amount) AS total_spent,
RANK() OVER (ORDER BY SUM(amount) DESC) AS spend_rank
FROM orders
WHERE customer_id IS NOT NULL
AND status <> 'Cancelled'
GROUP BY customer_id;+-------------+-------------+------------+
| customer_id | total_spent | spend_rank |
+-------------+-------------+------------+
| 101 | 8100.00 | 1 |
| 102 | 7900.00 | 2 |
| 103 | 7300.00 | 3 |
| 104 | 950.00 | 4 |
+-------------+-------------+------------+
4 rows in set (0.00 sec)Step by step: WHERE removes the cancelled order 1003 and the guest order 1008; GROUP BY makes one row per customer (101: 2500 + 5600 = 8100); then RANK numbers those four group rows. Without the status filter, customer 101's total would be inflated to 9,300 by an order that was never paid, so always decide what should be counted before you rank.
Removing Duplicates with ROW_NUMBER
A favourite interview task: "a table has duplicate rows, keep one copy of each and delete the rest". ROW_NUMBER solves it neatly. We will create a small demo table (in a copy of the database, never in the shared one):
CREATE TABLE signups (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(100),
signed_on DATE
);
INSERT INTO signups (email, signed_on) VALUES
('rahul@mail.com','2024-01-05'), ('kavya@mail.com','2024-01-06'),
('rahul@mail.com','2024-01-09'), ('neha@mail.com','2024-01-10'),
('rahul@mail.com','2024-01-12'), ('kavya@mail.com','2024-01-15');
SELECT id, email, signed_on,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY signed_on, id) AS rn
FROM signups
ORDER BY email, rn;+----+----------------+------------+----+
| id | email | signed_on | rn |
+----+----------------+------------+----+
| 2 | kavya@mail.com | 2024-01-06 | 1 |
| 6 | kavya@mail.com | 2024-01-15 | 2 |
| 4 | neha@mail.com | 2024-01-10 | 1 |
| 1 | rahul@mail.com | 2024-01-05 | 1 |
| 3 | rahul@mail.com | 2024-01-09 | 2 |
| 5 | rahul@mail.com | 2024-01-12 | 3 |
+----+----------------+------------+----+
6 rows in set (0.00 sec)Every row with rn > 1 is a later duplicate. Delete exactly those:
DELETE FROM signups
WHERE id IN (
SELECT id FROM (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY signed_on, id) AS rn
FROM signups
) AS t
WHERE rn > 1
);Query OK, 3 rows affected (0.01 sec)SELECT * FROM signups;+----+----------------+------------+
| id | email | signed_on |
+----+----------------+------------+
| 1 | rahul@mail.com | 2024-01-05 |
| 2 | kavya@mail.com | 2024-01-06 |
| 4 | neha@mail.com | 2024-01-10 |
+----+----------------+------------+
3 rows in set (0.00 sec)The earliest signup for each email survived. The extra level of nesting (SELECT id FROM (...) AS t) is a MySQL detail: MySQL does not let a DELETE read from the same table in a plain subquery, but a derived table is fully computed first, so it is allowed. The Top SQL Interview Queries chapter shows other ways to remove duplicates.
NTILE: Splitting Rows into Buckets
NTILE(n) divides the ordered rows into n buckets of (almost) equal size and returns the bucket number. It is used for quartiles, deciles, "top 25% of customers", and similar questions.
SELECT name, salary,
NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;+--------------+-----------+----------+
| name | salary | quartile |
+--------------+-----------+----------+
| Aarav Sharma | 150000.00 | 1 |
| Sneha Kapoor | 110000.00 | 1 |
| Priya Iyer | 95000.00 | 1 |
| Rohan Mehta | 95000.00 | 2 |
| Arjun Reddy | 82000.00 | 2 |
| Karan Patel | 70000.00 | 2 |
| Vikram Singh | 62000.00 | 3 |
| Ananya Das | 58000.00 | 3 |
| Ishita Gupta | 55000.00 | 4 |
| Meera Nair | 48000.00 | 4 |
+--------------+-----------+----------+
10 rows in set (0.00 sec)How the sizes are chosen: 10 rows / 4 buckets = 2 with remainder 2. Every bucket gets 2 rows, and the remainder (2 extra rows) goes to the first buckets, one each. So the sizes are 3, 3, 2, 2.
Notice that NTILE, like ROW_NUMBER, ignores ties: Priya and Rohan earn the same but land in different quartiles. NTILE counts rows, not values.
PERCENT_RANK and CUME_DIST
Two more functions describe a row's relative position as a number between 0 and 1:
PERCENT_RANK()= (RANK − 1) / (rows in partition − 1). The first row is always 0, and the last row is 1 if it has no ties.CUME_DIST()= (rows with a value less than or equal to this row's value) / (rows in partition). It is "what fraction of rows are at or below me".
SELECT name, salary,
RANK() OVER w AS rnk,
ROUND(PERCENT_RANK() OVER w, 3) AS pct_rank,
ROUND(CUME_DIST() OVER w, 3) AS cum_dist
FROM employees
WINDOW w AS (ORDER BY salary);+--------------+-----------+-----+----------+----------+
| name | salary | rnk | pct_rank | cum_dist |
+--------------+-----------+-----+----------+----------+
| Meera Nair | 48000.00 | 1 | 0 | 0.1 |
| Ishita Gupta | 55000.00 | 2 | 0.111 | 0.2 |
| Ananya Das | 58000.00 | 3 | 0.222 | 0.3 |
| Vikram Singh | 62000.00 | 4 | 0.333 | 0.4 |
| Karan Patel | 70000.00 | 5 | 0.444 | 0.5 |
| Arjun Reddy | 82000.00 | 6 | 0.556 | 0.6 |
| Priya Iyer | 95000.00 | 7 | 0.667 | 0.8 |
| Rohan Mehta | 95000.00 | 7 | 0.667 | 0.8 |
| Sneha Kapoor | 110000.00 | 9 | 0.889 | 0.9 |
| Aarav Sharma | 150000.00 | 10 | 1 | 1 |
+--------------+-----------+-----+----------+----------+
10 rows in set (0.00 sec)Check Priya's row by hand: her RANK is 7, so PERCENT_RANK = (7 − 1) / (10 − 1) = 6/9 = 0.667. Eight salaries are ≤ 95,000 (both 95,000 rows count), so CUME_DIST = 8/10 = 0.8. This time the window sorts ascending, so a high value means "earns more than most".
Did you notice the alias is cum_dist, not cume_dist? That is on purpose; see the next section.
Common Mistakes
1. Using a function name as an alias
In MySQL 8, RANK, DENSE_RANK, ROW_NUMBER, CUME_DIST, PERCENT_RANK, NTILE and others are reserved words. This query is a syntax error:
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank
FROM employees' at line 1Pick another alias (rnk, salary_rank) or quote it with backticks: AS `rank`. Queries copied from older MySQL 5.7 code sometimes break for exactly this reason.
2. Forgetting ORDER BY inside OVER
Ranking without an order is meaningless. In MySQL, RANK() OVER () is accepted but treats every row as tied:
SELECT name, salary, RANK() OVER () AS r FROM employees LIMIT 3;+--------------+-----------+---+
| name | salary | r |
+--------------+-----------+---+
| Aarav Sharma | 150000.00 | 1 |
| Priya Iyer | 95000.00 | 1 |
| Rohan Mehta | 95000.00 | 1 |
+--------------+-----------+---+
3 rows in set (0.00 sec)No error, just a useless answer. Always give ranking functions an ORDER BY.
3. Filtering on the rank in WHERE or HAVING
As shown above, wrap the query in a CTE or derived table and filter outside.
4. Using ROW_NUMBER when ties matter (or RANK when they don't)
"Top 2 per department" with ROW_NUMBER silently drops a tied employee. "Exactly one row per customer" with RANK can return two rows when there is a tie. Decide which behaviour the question wants.
5. Expecting ranks to follow NULLs the way you want
In MySQL, NULL sorts first in ascending order and last in descending order, and NULLs tie with each other. Ranking employees by city ascending puts Ishita (city NULL) at rank 1. Filter NULLs out with WHERE, or sort with ORDER BY city IS NULL, city inside OVER to push them to the end.
6. Confusing the two ORDER BYs
The ORDER BY inside OVER decides the numbers. The ORDER BY at the end of the query decides the display order. A ranked result may come back in any order unless you add a final ORDER BY.
Choosing the Right Function
Task | Best choice |
|---|---|
Nth highest distinct value |
|
Leaderboard where ties share a place and the next place is skipped |
|
Exactly one row per group (latest order, first login) |
|
Delete duplicates, keep one |
|
Pagination or numbering lines in a report |
|
Quartiles, deciles, equal-sized buckets |
|
Percentile position of a row |
|
Interview Questions
What is the difference between ROW_NUMBER, RANK and DENSE_RANK?
All number rows in window order. ROW_NUMBER gives every row a unique number even for ties. RANK gives tied rows the same number and skips the following numbers (1, 2, 2, 4). DENSE_RANK gives tied rows the same number without gaps (1, 2, 2, 3).
When do the three functions return the same values?
When the window's ORDER BY values are all distinct within each partition, that is, when there are no ties.
How do you find the Nth highest salary using window functions?
Compute DENSE_RANK() OVER (ORDER BY salary DESC) in a CTE or derived table and select rows where it equals N. DENSE_RANK makes duplicate salaries count once.
Why can't you write WHERE ROW_NUMBER() OVER (...) = 1?
Window functions are evaluated after WHERE, GROUP BY and HAVING, so their values do not exist yet when WHERE runs. Compute them in a subquery or CTE and filter in the outer query.
How do you get the top 3 products in each category?
Use ROW_NUMBER() (exactly 3) or DENSE_RANK() (include ties) with PARTITION BY category ORDER BY sales DESC, then filter <= 3 in an outer query.
Is ROW_NUMBER deterministic?
Only if the window's ORDER BY uniquely orders the rows. Otherwise tied rows can be numbered differently between runs; add a unique tie-breaker such as the primary key.
How would you delete duplicate rows but keep one?
Number rows with ROW_NUMBER() OVER (PARTITION BY duplicate_columns ORDER BY id) and delete rows whose number is greater than 1 (in MySQL, through a derived table).
What does NTILE(4) do with 10 rows?
It creates 4 buckets of sizes 3, 3, 2, 2: the remainder rows go to the first buckets.
What is the difference between PERCENT_RANK and CUME_DIST?
PERCENT_RANK = (rank − 1)/(n − 1), so the first row is 0. CUME_DIST = (rows ≤ current value)/n, so it is never 0 and the last row is always 1.
Summary
ROW_NUMBER(),RANK()andDENSE_RANK()number rows in the order given insideOVER(); they differ only for ties.ROW_NUMBER: always unique (1, 2, 3, 4). RANK: ties share, gaps follow (1, 2, 2, 4). DENSE_RANK: ties share, no gaps (1, 2, 2, 3).
PARTITION BYrestarts the numbering for each group, which gives "top N per group" and "latest row per group".Window functions are evaluated after WHERE/GROUP BY/HAVING, so filter on a rank in an outer query (CTE or derived table).
DENSE_RANK is the safe choice for "Nth highest value"; ROW_NUMBER with a tie-breaker is the choice for "exactly one row".
NTILE(n)makes n near-equal buckets;PERCENT_RANKandCUME_DISTgive relative positions from 0 to 1.Function names like RANK are reserved words in MySQL 8; do not use them as unquoted aliases.
Next, in Transactions in SQL, we leave queries behind and learn how to group several changes so that they either all happen or none of them do.