SQL RANK(): How to Organize Rows with Examples

When working with SQL, sorting data is straightforward since you can easily use ORDER BY to arrange rows. However, simply ordering results doesn’t tell you the position of each row within that order. This is where ranking becomes useful. Instead of just viewing sorted data, you may want to assign a position like 1st, 2nd, or 3rd to each row based on specific criteria.

The RANK() function solves this problem by assigning a numerical rank to each row according to the defined order. RANK() is part of SQL window functions, which allow you to perform calculations across a set of rows while still returning individual row results. 

In this tutorial, I will show you the syntax of RANK(), practical examples, understand how it handles ties, and see how it compares to similar functions like DENSE_RANK() and ROW_NUMBER().

If you are new to SQL, start with our Introduction to SQL course, or the Intermediate SQL course if you have some experience. 

What Is the SQL RANK() Function?

The SQL RANK() function is a window function that assigns a rank to each row based on a specified order. Ranking is entirely determined by the ORDER BY inside the window function. So, it gives each row a position like 1, 2, 3, and so on, according to the ORDER BY clause.

You should note that when you use RANK():

• Rows with the same value (ties) receive the same rank.
• When ties occur, gaps appear in the ranking sequence. For example, if two rows are ranked 2, the next rank will be 4 and not 3.

SQL RANK() Syntax

The basic syntax of the RANK() function is:

RANK() OVER (ORDER BY column)

Where:

• OVER clause: Defines the window (set of rows) the function operates on.

• ORDER BY: Determines how the ranking is applied, such as from highest to lowest.

• PARTITION BY (optional): Splits data into groups, ranking rows separately within each group.

Below is the syntax of the RANK() function with the PARTITION BY clause:

RANK() OVER (PARTITION BY column1 ORDER BY column2)

The SQL RANK() function is widely supported across major databases. These include PostgreSQL, MySQL (8.0+), Microsoft SQL Server, and Oracle Database. The syntax is mostly consistent across these systems, since it’s part of the standard ANSI SQL specification.

Basic Example of SQL RANK()

Now that you have understood the syntax of the RANK() function, let me show you a simple example of how it works.

Assume you have the employees table with the salary information of each employee.

You can use the following query to rank the employees according to their salaries.

-- Rank employees by salary
SELECT 
    name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank_position
FROM employees;

In the above query, the rows are ordered by salary from the highest, so the highest salary gets rank 1. When two rows have the same salary, they receive the same rank.

You will notice that if two rows have the same values, they receive the same rank.

Then the next rank is skipped. SQL accounts for the tie by skipping the next rank value.

I recommend taking our Introduction to SQL Server course to learn more about grouping and data aggregation.

SQL RANK() with PARTITION BY

As we have learned earlier, the PARTITION BY clause lets you apply ranking within groups instead of across the entire dataset.

In the example below, employees are grouped by department, and the ranking starts fresh within each department. Therefore, each group has its own independent ranking sequence.

-- Rank employees within each department
SELECT 
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

From the above results, we can see that in “Sales”, both Emily Johnson and Michael Brown share rank 1, while in “HR”, William Miller and Olivia Wilson also share rank 1.

SQL RANK() vs. DENSE_RANK() vs. ROW_NUMBER()

When ranking data in SQL, you can choose to use RANK(), DENSE_RANK(), or ROW_NUMBER(). These functions may look similar, but behave as follows:

• RANK(): Ties share the same rank, hence gaps appear after ties.

• DENSE_RANK(): Ties share the same rank, hence no gaps in ranking.

• ROW_NUMBER(): Every row gets a unique number with no ties, even if values are equal.

To better understand these differences, let’s do a side-by-side comparison using the query below:

-- Compare RANK() vs DENSE_RANK() vs ROW_NUMBER()
SELECT 
    name,
    salary,

    RANK() OVER (ORDER BY salary DESC) AS rank_val,          -- allows gaps after ties

    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank1,  -- no gaps, consecutive ranks

    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num      -- always unique sequence

FROM employees;

From the above results, you can choose to:

• Use RANK() when position matters, and gaps are acceptable.

• Use DENSE_RANK() when you want a continuous ranking.

• Use ROW_NUMBER() when every row must have a unique number, such as in data pagination.

When to Use SQL RANK()

SQL RANK() function is most useful when you want to order data while handling ties naturally. I also find it important for the following use cases:

• Leaderboards: When you want to rank players or users based on scores, to allow ties for equal performance.
• Ranking sales performance: if you want to identify top-performing employees or regions by revenue, even when results are equal.
• Identifying top-N values: Its useful for queries like “top 3 salaries,” especially when ties should be included.

Common Mistakes with SQL RANK()

Here are some mistakes I have encountered when using the SQL RANK() function, and how you can avoid them:

• Forgetting ORDER BY: Without the ORDER BY clause, ranking has no defined logic and may return unexpected results.

• Misunderstanding tie behavior: As a beginner, you may expect 1, 2, 2, 3, but RANK() produces gaps (1, 2, 2, 4) in the ranks.

• Using RANK() when ROW_NUMBER() is needed: If you need unique sequential values, ROW_NUMBER() is the better choice.

• Not using PARTITION BY when needed: Without partitioning, ranking is applied globally instead of within groups.

Best Practices for Using SQL RANK()

To keep your queries clean and ensure you get the expected results, I recommend you follow these best practices when using the SQL RANK() function:

• Always define clear ordering: Be explicit with the ORDER BY clause, like DESC, to avoid ambiguous rankings.

• Choose the correct ranking function: Use RANK(), DENSE_RANK(), or ROW_NUMBER() based on how you want ties handled.

• Test tie scenarios: Before pushing to production, verify how your data behaves when duplicate values exist.

• Combine with filtering: Since you cannot use RANK() directly in a WHERE clause, you must wrap your ranking query in a Subquery or CTE when you want to filter “Top N.”

Conclusion

RANK() is a useful tool for making ordered comparisons in SQL, especially when you need results that reflect real-world scenarios like equal values. Understanding how it handles ties and why ranks can repeat and skip numbers is important for accurate analysis. Once you master this behavior, you can know when to choose it alongside alternatives like DENSE_RANK() and ROW_NUMBER(), based on what best fits your specific use case.

Now that you have learned how to rank data in SQL, I recommend you check out our Associate Data Analyst in SQL career track if interested in becoming a proficient data analyst to learn the necessary skills. Our Reporting in SQL course is also appropriate if you want to learn how to build professional dashboards using SQL.