The SQL RANK() function is a window function that assigns a rank to each row within a partition of a result set. It is used to rank employees by salary, students by marks, products by sales, and other records. The RANK() function assigns the same rank to rows with equal values. When two or more rows are tied for a rank, the next rank is skipped. For example, if two students are tied for first place, they both receive rank 1 and the next student receives rank 3.
In this article, we will learn what the SQL RANK() function is, its syntax, how it works, and how to use it with practical examples. If you are new to databases, start with our SQL Tutorial first.
What is the RANK() Function in SQL?
The RANK() function in SQL is a window function that assigns a numerical position to each row within a result set according to a specified sorting order. It is useful when we need to compare records and identify their positions based on a particular value.
For example, suppose a company wants to rank its employees according to their salaries. The employee with the highest salary will receive rank 1. If two employees have the same salary, they will receive the same rank.
Unlike SQL aggregate functions such as SUM() or COUNT(), which combine many rows into one result, RANK() keeps every row and simply adds a ranking column next to it.
Key points of the SQL RANK() function:
- It assigns a numerical rank to each row.
- Rows with equal values receive the same rank.
- It skips subsequent ranks when duplicate values occur.
- It works with the OVER() clause.
- It supports PARTITION BY and ORDER BY.
- It does not change or remove the original rows from the result set.
Syntax
The basic syntax of the RANK() function is:
RANK() OVER (
[PARTITION BY column_name]
ORDER BY column_name [ASC | DESC]
)
The ORDER BY inside OVER() works just like the SQL ORDER BY clause, and PARTITION BY divides rows into groups in a similar way to GROUP BY, except that the rows are not collapsed.
Example of RANK() Function in SQL
Let’s understand the RANK() function with a practical example. Suppose we have an Employees table containing employee IDs, names, departments, and salaries. (If you are not sure how tables are created, read Databases and Tables in SQL.)
Employees Table
| Employee_ID | Employee_Name | Department | Salary |
|---|---|---|---|
| 101 | Amit | IT | 60000 |
| 102 | Rahul | HR | 45000 |
| 103 | Priya | IT | 75000 |
| 104 | Neha | Sales | 60000 |
| 105 | Karan | HR | 50000 |
Now, we want to rank employees based on their salaries, where the highest salary receives rank 1. We use a basic SELECT statement and add RANK() as an extra column.
SQL Query:
SELECT
Employee_ID,
Employee_Name,
Salary,
RANK() OVER (ORDER BY Salary DESC) AS Salary_Rank
FROM Employees;
Output:
| Employee_ID | Employee_Name | Salary | Salary_Rank |
|---|---|---|---|
| 103 | Priya | 75000 | 1 |
| 101 | Amit | 60000 | 2 |
| 104 | Neha | 60000 | 2 |
| 105 | Karan | 50000 | 4 |
| 102 | Rahul | 45000 | 5 |
Amit and Neha have the same salary, so both get rank 2. Rank 3 is skipped and Karan gets rank 4.
RANK() Function with PARTITION BY
The PARTITION BY clause is used with the RANK() function to divide records into separate groups and rank each group independently. For example, suppose we want to rank employees based on their salaries within each department.
SQL Query:
SELECT
Employee_ID,
Employee_Name,
Department,
Salary,
RANK() OVER (
PARTITION BY Department
ORDER BY Salary DESC
) AS Department_Rank
FROM Employees;
Output:
| Employee_ID | Employee_Name | Department | Salary | Department_Rank |
|---|---|---|---|---|
| 103 | Priya | IT | 75000 | 1 |
| 101 | Amit | IT | 60000 | 2 |
| 105 | Karan | HR | 50000 | 1 |
| 102 | Rahul | HR | 45000 | 2 |
| 104 | Neha | Sales | 60000 | 1 |
The rank restarts from 1 for every department.
RANK() Function with ASC and DESC
The RANK() function can use both ascending and descending order to rank records according to different requirements.
1. Using ASC (Ascending Order)
The ASC keyword sorts values from the lowest to the highest.
SELECT
Employee_Name,
Salary,
RANK() OVER (ORDER BY Salary ASC) AS Salary_Rank
FROM Employees;
In this example, the employee with the lowest salary receives rank 1.
2. Using DESC (Descending Order)
The DESC keyword sorts values from the highest to the lowest.
SELECT
Employee_Name,
Salary,
RANK() OVER (ORDER BY Salary DESC) AS Salary_Rank
FROM Employees;
In this example, the employee with the highest salary receives rank 1.
Difference Between RANK(), DENSE_RANK(), and ROW_NUMBER() in SQL
SQL provides three ranking functions: RANK(), DENSE_RANK(), and ROW_NUMBER(). All three assign numerical values to rows, but they handle duplicate values differently.
Suppose we have the following student marks:
| Student | Marks |
|---|---|
| Amit | 95 |
| Rahul | 90 |
| Priya | 90 |
| Neha | 85 |
SQL Query:
SELECT
Student,
Marks,
RANK() OVER (ORDER BY Marks DESC) AS Rank_No,
DENSE_RANK() OVER (ORDER BY Marks DESC) AS Dense_Rank_No,
ROW_NUMBER() OVER (ORDER BY Marks DESC) AS Row_No
FROM Students;
Output:
| Student | Marks | RANK() | DENSE_RANK() | ROW_NUMBER() |
|---|---|---|---|---|
| Amit | 95 | 1 | 1 | 1 |
| Rahul | 90 | 2 | 2 | 2 |
| Priya | 90 | 2 | 2 | 3 |
| Neha | 85 | 4 | 3 | 4 |
Differences:
| Function | How it works |
|---|---|
| RANK() | Assigns the same rank to duplicate values and skips the next rank. |
| DENSE_RANK() | Assigns the same rank to duplicate values without skipping the next rank. |
| ROW_NUMBER() | Assigns a unique sequential number to every row. |
If you only need the unique values from a column and not a ranking, the SQL DISTINCT keyword is the simpler choice.
Important Points About RANK() Function
- RANK() is a window function in SQL.
- It requires an OVER() clause.
- The ORDER BY clause determines the ranking order.
- The PARTITION BY clause is optional.
- Duplicate values receive the same rank.
- Subsequent ranks are skipped when duplicate values occur.
- It does not reduce the number of rows in the result set.
To filter ranked rows (for example, only the top 3 salaries), place the RANK() query in a subquery or CTE and apply a WHERE clause on the rank column, because window functions cannot be used directly in WHERE. To filter groups instead of rows, use the HAVING clause. When ranking data from multiple tables, combine RANK() with an SQL Join.
Frequently Asked Questions (FAQs)
1. What is the RANK() function in SQL?
The RANK() function is a window function that assigns a numerical rank to each row based on the specified sorting order. Rows with equal values receive the same rank, and subsequent ranks are skipped.
2. Does RANK() allow duplicate values in SQL?
Yes. The RANK() function allows duplicate values. Rows containing equal values receive the same rank.
3. What is the difference between RANK() and DENSE_RANK()?
The RANK() function skips subsequent ranks after duplicate values, whereas DENSE_RANK() does not skip ranks.
4. Can we use PARTITION BY with RANK()?
Yes. The PARTITION BY clause can be used with RANK() to divide records into groups and assign ranks separately within each group.
5. Is RANK() an aggregate function?
No. RANK() is a window function. It assigns ranking values to rows without combining them into a single result. You can compare it with SQL aggregate functions to see the difference.
6. Does RANK() start from 0 or 1?
The RANK() function starts ranking from 1 within each partition.
7. Can we use RANK() without ORDER BY?
Ranking has no meaning without an ORDER BY, so you should always include it inside the OVER() clause. Some databases, such as SQL Server, make it mandatory, while others will give every row rank 1 if it is missing.