Difference Between : RANK(), DENSE_RANK() and ROW_NUMBER() in SQL

As you dive into the world of SQL, you’ll encounter three essential ranking functions: ROW_NUMBER(), RANK(), and DENSE_RANK(). All three belong to the category of window functions in Microsoft SQL Server, and their distinct behavior comes into play when dealing with duplicate records. Let’s say you need to rank employees by salary, and two of them have identical salaries—how would their ranks be determined? The answer depends on the ranking function you choose: ROW_NUMBER(), RANK(), or DENSE_RANK().

ROW_NUMBER() guarantees a unique rank for each record, even when duplicates exist. When the ORDER BY clause can’t differentiate between two rows, they still receive distinct rankings assigned randomly. For instance, two employees, Shane and Rick, share the same salary and have row numbers 4 and 5, but this order could change on subsequent runs.

RANK() and DENSE_RANK() assign identical rankings to rows indistinguishable by the ORDER BY clause. However, while DENSE_RANK() generates a continuous sequence of ranks (1, 2, 3, …), RANK() leaves gaps after multiple rows with the same rank (similar to Olympic Games’ medal awards). To delve deeper into these functions, consider enrolling in the Querying Microsoft SQL Server course on Udemy.

Interestingly, these functions exhibit similar behavior in both Microsoft SQL Server and Oracle. So if you’ve used them in MSSQL, you can also apply them to Oracle 11g or other versions.

If you’re new to Microsoft SQL Server and T-SQL, we recommend joining a comprehensive course, such as Microsoft SQL for Beginners by Brewster Knowlton on Udemy. It’s a fantastic starting point for learning T-SQL and SQL queries in SQL Server.

When it comes to building a schema, you can create a table and insert data for demonstration purposes. For example, you can use the code provided to create an employee table with two employees sharing the same salary, Shane and Rick. This setup helps illustrate the differences between ROW_NUMBER(), RANK(), and DENSE_RANK() window functions in SQL Server when ties occur in rankings.

For those eager to learn more about ranking functions in SQL Server, we highly recommend the 70-461, 761: Querying Microsoft SQL Server with Transact-SQL course on Udemy. This course offers an in-depth look at SQL Server and helps prepare you to become a certified SQL Server DBA.

The examples provided showcase the unique behavior of ROW_NUMBER(), RANK(), and DENSE_RANK() when dealing with identical records. The differences become apparent when duplicate records are present. ROW_NUMBER() assigns consecutive numbers, while RANK() and DENSE_RANK() assign the same rank to duplicates. However, RANK() leaves gaps, whereas DENSE_RANK() maintains a continuous sequence.

ROW_NUMBER() Example:

ROW_NUMBER() generates a unique value for each row, even if they have identical values and the ORDER BY clause cannot differentiate between them. This function is useful for solving problems like finding the second-highest salary or the nth-highest salary.

Here’s an example using an employee table with two employees sharing the same salary:

-- Create sample employee table
CREATE TABLE Employee (name varchar(10), salary int);

INSERT INTO Employee VALUES ('Rick', 3000);
INSERT INTO Employee VALUES ('John', 4000);
INSERT INTO Employee VALUES ('Shane', 3000);
INSERT INTO Employee VALUES ('Peter', 5000);
INSERT INTO Employee VALUES ('Jackob', 7000);
INSERT INTO Employee VALUES ('Sid', 1000);

-- Use ROW_NUMBER() to generate unique row numbers based on salary
SELECT e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) row_number
FROM Employee e;

Result:

name    salary  row_number
Jackob  7000    1
Peter   5000    2
John    4000    3
Shane   3000    4
Rick    3000    5
Sid     1000    6

In this example, employees are ranked based on their salaries. Each employee has a unique rank, even if their salaries are the same. For instance, Shane and Rick both have a salary of 3000, but they are assigned unique row numbers 4 and 5. Note that when ties occur, row numbers are assigned randomly.

Lear more:- How to Implement dwave qbsolve in Python? [Solved]

RANK() Example:

The RANK() function assigns the same rank to rows with identical values, which cannot be distinguished by the ORDER BY clause. Also, the next distinct rank will not start from the immediately succeeding number; instead, there will be a gap. For example, if the 4th and 5th employees have the same salary, they will both be assigned rank 4, and the 6th employee with a different salary will be assigned rank 6.

Here’s an example to illustrate this:

-- Create sample employee table
CREATE TABLE Employee (name varchar(10), salary int);

INSERT INTO Employee VALUES ('Rick', 3000);
INSERT INTO Employee VALUES ('John', 4000);
INSERT INTO Employee VALUES ('Shane', 3000);
INSERT INTO Employee VALUES ('Peter', 5000);
INSERT INTO Employee VALUES ('Jackob', 7000);
INSERT INTO Employee VALUES ('Sid', 1000);

-- Use RANK() to generate ranks based on salary
SELECT e.*, RANK() OVER (ORDER BY salary DESC) rank
FROM Employee e;
name    salary  rank
Jackob  7000    1
Peter   5000    2
John    4000    3
Shane   3000    4
Rick    3000    4
Sid     1000    6

As you can see, both Shane and Rick have the same salary of 3000 and are assigned the same rank of 4. Sid, however, is assigned rank 6 instead of 5 because RANK() maintains the original ordering.

SQL to build schema

Here’s the SQL code to create a sample employee table schema and insert some data for demonstration purposes:

-- Check if the Employee table exists and drop it
IF OBJECT_ID('Employee') IS NOT NULL
DROP TABLE Employee;

-- Create the Employee table
CREATE TABLE Employee (name varchar(10), salary int);

-- Insert sample data into the Employee table
INSERT INTO Employee VALUES ('Rick', 3000);
INSERT INTO Employee VALUES ('John', 4000);
INSERT INTO Employee VALUES ('Shane', 3000);
INSERT INTO Employee VALUES ('Peter', 5000);
INSERT INTO Employee VALUES ('Jackob', 7000);
INSERT INTO Employee VALUES ('Sid', 1000);

In this example, the Employee table contains two employees with the same salary (Shane and Rick), which helps demonstrate the difference between ROW_NUMBER(), RANK(), and DENSE_RANK() window functions in SQL.

Read more:- How to Develop a Morse Code Translator Mobile App in 2023?

Conclusion

In conclusion, understanding the difference between rank and dense_rank, as well as dense_rank vs row_number, is crucial for effective SQL querying. Rank vs dense_rank is primarily distinguished by how they handle ties, with rank leaving gaps while dense_rank assigns continuous ranks. Comparing dense_rank vs rank, both functions assign the same rank to tied values, but dense_rank ensures that the next unique value will have a rank that is immediately incremented, whereas rank leaves a gap in the ranking sequence. When comparing dense_rank vs row_number, row_number will always generate unique rankings for each row, even when there are ties. Therefore, it’s essential to know the appropriate function to use based on your specific needs and understanding the difference between rank and dense_rank, as well as dense_rank vs row_number, can help you achieve the desired results in your SQL queries.

Leave a Comment

9 + three =