Home › SQL Tutorial › SQL RANK() Function: Syntax, Examples and Difference

SQL RANK() Function: Syntax, Examples and Difference

⏱ 6 min read Updated: 05 Nov 2026

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_IDEmployee_NameDepartmentSalary
101AmitIT60000
102RahulHR45000
103PriyaIT75000
104NehaSales60000
105KaranHR50000

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_IDEmployee_NameSalarySalary_Rank
103Priya750001
101Amit600002
104Neha600002
105Karan500004
102Rahul450005

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_IDEmployee_NameDepartmentSalaryDepartment_Rank
103PriyaIT750001
101AmitIT600002
105KaranHR500001
102RahulHR450002
104NehaSales600001

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:

StudentMarks
Amit95
Rahul90
Priya90
Neha85

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:

StudentMarksRANK()DENSE_RANK()ROW_NUMBER()
Amit95111
Rahul90222
Priya90223
Neha85434

Differences:

FunctionHow 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.