How to Rank Rows in SQL: Window Functions and Ranking Methods

SQL ranking assigns a number to each row based on an order you choose

When you need to number rows in a result set — say, ranking sales by amount or numbering employees by hire date — SQL gives you window functions that do this without collapsing your data into groups. The most common are ROW_NUMBER(), RANK(), and DENSE_RANK(). Each behaves differently when rows have the same value, and which one you pick depends on whether you want gaps in your numbering or not.

All three sit in the OVER() clause, which tells SQL how to partition and order your data. You write them in the SELECT statement alongside your other columns, and they calculate a rank for every row without removing any rows from the result.

Key Takeaways

ROW_NUMBER() gives every row a unique number with no gaps, even when values are identical.
RANK() skips numbers after ties, so if two rows tie for first place, the next rank is third.
DENSE_RANK() also handles ties but does not skip numbers, so the next rank after a tie for first is second.
The OVER() clause controls how rows are grouped and ordered, and you can rank within separate groups using PARTITION BY.
Window functions run after WHERE filters but before ORDER BY, so you can filter results and then rank what remains.

ROW_NUMBER() assigns a unique integer to every row

ROW_NUMBER() is the simplest ranking function. It counts up from 1, giving each row a different number even if the values you are ordering by are the same. If you have two employees hired on the same day, one gets 1 and the other gets 2.

Here is a basic example:

SELECT employee_name, hire_date, ROW_NUMBER() OVER (ORDER BY hire_date) AS rank FROM employees;

This returns every employee with a rank based on hire date. The first hired gets rank 1, the second gets rank 2, and so on. If two employees share the same hire date, SQL assigns them consecutive numbers in whatever order the database encounters them — the order is not may provide unless you add a tiebreaker column to the ORDER BY clause.

ROW_NUMBER() is useful when you straightforward need to number rows for pagination or to identify a specific row in a sorted list. It does not care about the actual values; it only counts position.

RANK() and DENSE_RANK() handle ties differently

When rows have identical values in the column you are ordering by, RANK() and DENSE_RANK() both acknowledge the tie but handle the next rank differently.

RANK() skips numbers after a tie. If two employees tie for first place in sales, they both get rank 1, and the next employee gets rank 3 (skipping rank 2). This is useful when you care about position relative to others — like a sports leaderboard where two athletes tie for gold and the next gets bronze.

SELECT employee_name, sales_amount, RANK() OVER (ORDER BY sales_amount DESC) AS rank FROM employees;

DENSE_RANK() does not skip numbers. If two employees tie for rank 1, the next gets rank 2, not rank 3. Use this when you want consecutive numbering but still want to show that a tie occurred.

SELECT employee_name, sales_amount, DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS rank FROM employees;

The choice between them depends on your data story. A tournament bracket uses RANK(). A report showing performance tiers uses DENSE_RANK().

PARTITION BY ranks within separate groups

The OVER() clause accepts a PARTITION BY argument that lets you rank separately within each group. Instead of one ranking across all rows, you get a fresh ranking for each partition.

For example, rank employees within each department:

SELECT department, employee_name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees;

Now each department has its own ranking. The highest-paid person in Sales gets rank 1, and the highest-paid person in Engineering also gets rank 1. Without PARTITION BY, only one person across the entire company would have rank 1.

You can partition by multiple columns. PARTITION BY department, job_title creates a separate ranking for each combination of department and job title. This is common when you need to compare performance within meaningful groups rather than across the entire dataset.

Combine ranking with WHERE and ORDER BY for filtered results

Window functions execute after WHERE filters but before the final ORDER BY. This means you can filter your data first, then rank what remains.

SELECT employee_name, department, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees WHERE department = 'Sales' ORDER BY rank;

This query filters to only Sales employees, ranks them by salary, and then sorts the result by rank. The ranking only includes the filtered rows, not the entire company.

If you want to rank all employees but then filter to only the top 5, you need a subquery or a common table expression (CTE) because you cannot use window function results in a WHERE clause directly. Write the ranking in an inner query, then filter the outer query:

SELECT * FROM (SELECT employee_name, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees) AS ranked WHERE rank <= 5;

Use NTILE() to divide rows into equal buckets

NTILE() divides your rows into a specified number of roughly equal groups. NTILE(4) splits rows into quartiles, NTILE(10) into deciles. Each row gets a bucket number from 1 to N.

SELECT employee_name, salary, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees;

This assigns each employee to a quartile based on salary. The lowest 25 percent get bucket 1, the next 25 percent get bucket 2, and so on. NTILE() is useful for segmentation — identifying top performers, bottom performers, or middle tiers without having to calculate percentiles manually.

If your dataset does not divide evenly, NTILE() distributes the remainder across the first buckets. With 10 rows and NTILE(3), the first bucket gets 4 rows, and the other two get 3 each.

LAG() and LEAD() compare a row to its neighbors

LAG() and LEAD() are window functions that look at other rows in the ordered set. LAG() returns a value from the previous row, and LEAD() returns a value from the next row. They are not ranking functions, but they work with the same OVER() syntax and solve related problems.

SELECT employee_name, salary, LAG(salary) OVER (ORDER BY salary) AS previous_salary, LEAD(salary) OVER (ORDER BY salary) AS next_salary FROM employees;

This shows each employee's salary alongside the salary of the employee ranked just below and just above them. You can use this to calculate differences, spot outliers, or show progression.

Both functions accept an offset argument. LAG(salary, 2) looks back two rows instead of one. They also accept a default value if there is no row to look at — LAG(salary, 1, 0) returns 0 if there is no previous row.

Frequently Asked Questions

What is the difference between ROW_NUMBER and RANK?

ROW_NUMBER() gives every row a unique number with no gaps. RANK() assigns the same number to rows with identical values and then skips numbers. If two rows tie for rank 1, ROW_NUMBER() still gives them 1 and 2, but RANK() gives them both 1 and the next row gets 3.

Can I use a window function in a WHERE clause?

No. Window functions are calculated after WHERE filters run, so you cannot filter based on a window function result directly. Use a subquery or CTE to calculate the rank first, then filter the outer query by the rank column.

Do I have to use PARTITION BY?

No. PARTITION BY is optional. Without it, your ranking runs across all rows. With it, you get a separate ranking for each group. Use PARTITION BY when you need to rank within categories, like sales by region or students by grade level.

What happens if I do not include ORDER BY in the OVER clause?

The ranking becomes unpredictable because SQL has no rule for the order. Always include ORDER BY in the OVER() clause to specify how rows should be ranked. You can have a separate ORDER BY at the end of the query to sort the final result differently.

Can I rank by multiple columns?

Yes. Use ORDER BY column1, column2 in the OVER() clause. Rows are ranked first by column1, and ties are broken by column2. This is useful when you want a tiebreaker — for example, rank by sales amount, then by hire date to break ties.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.