RANK() Function in SQL Server

RANK()

The RANK() in SQL gives the rank to every row in a result. RANK returns the same numerical value for ties (for instance, 1, 2, 2, 4, 5).

The primary syntax of the RANK() is the following:

RANK() OVER (
	[PARTITION BY <expression>]
	ORDER BY <expression> [ASC|DESC]
)

In the above syntax:

• The PARTITION BY clause separates the rows of the result set into partitions before applying the function. It is optional to write.
   
• Second, the partitions to which the function is applied are logically sorted according to the ORDER BY clause.

Let us see an example of the RANK() in SQL. We will be using the above-mentioned table as a reference.

SELECT first_name, last_name, id,
	RANK () OVER (
		ORDER BY id
	) AS RANK_NO
FROM rank_final;

Let us see what this query will result in.

This output demonstrates that several rows receive the same rank when the id field contains the same value. The following ranking number will be its previous rank plus the number of duplicate numbers.

Now let us see the next rank function in SQL.

ROW_NUMBER()

This is the simplest ranking function available in SQL. Just as it sounds, the function ROW_NUMBER() displays the number of a specified row. It is a ranking function that gives a different number to every row it applies to (rows can be in the partition or all of the rows from the result set). It starts at one and then ranks the rows accordingly.

ROW_NUMBER assigns different rankings to the numerous rows with the same value on which ranking needs to be done.

The basic syntax of the ROW_NUMBER() is the following:

ROW_NUMBER() OVER (
	[PARTITION BY <expression>]
	ORDER BY <expression> [ASC|DESC]
)

Let us see an example of the ROW_NUMBER() in SQL.

SELECT first_name, last_name, id,
	ROW_NUMBER() OVER (
		ORDER BY id
	) AS Rank_No
FROM rank_final;

The above-mentioned query results are in the following table.

 

According to their id, the ranking for the table will be assigned. Here, we can see that it assigns various ranks to a row of data that contains the same id value. Let us proceed to the following section of the blog and discuss the DENSE_RANK() in SQL.

DENSE_RANK()

The DENSE_RANK() method additionally assigns a rank to each row in accordance with a predetermined ordering. The defining result of the DENSE_RANK() function is that it will apply the same rank to values that match but will not allow gaps in the ranking.

As an outcome, even though it may be in the third row, the next ranked value will earn rank two if two matching values obtain rank one. As a result, you cannot be certain that the ranking and number of rows are identical when the rank increases.

The basic syntax of the DENSE_RANK() is the following:

DENSE_RANK()  OVER (
	[PARTITION BY <expression>]
	ORDER BY <expression> [ASC|DESC]
)

Let us see an example of the DENSE_RANK() in SQL.

SELECT first_name, last_name, id, 
	DENSE_RANK() OVER (
		ORDER BY id
	) AS Rank_No
FROM rank_final;

This query will result in the following table.

 

The output shown above demonstrates that duplicate values have the same rank, and the next rank is the next sequential integer.

NTILE Function

The next function for the rank function in SQL is the NTILE function. The method makes it easier to group rows. Based on the given criteria, a rank is given to each row group. The NTILE() method specifies the number of groups.

The basic syntax of the NTILE function is the following:

NTILE(N) OVER (
	[PARTITION BY <expression>]
	ORDER BY <expression> [ASC|DESC]
)

Let us see an example of the NTILE function in SQL.

SELECT first_name, last_name, id, 
	NTILE(4) OVER (
		ORDER BY id
	) AS Rank_No
FROM rank_final;

Where N is the parameter given by the user in the NTILE function.

The above query will show the following result.

There are eight records in the specified table. In the output above, the NTILE(4) specifies that a group of four records must be present in the result set.

Now let us examine some frequently asked questions.

Also read, Natural Join in SQL

Examples of RANK() Function in SQL

The RANK() function in SQL is used to assign a rank to each row within the result set based on the values in one or more columns. It is often used for ranking or ordering data.

Here's an explanation with examples:

Suppose you have a table named "Scores" with the following data:

You can use the RANK() function to rank the students based on their scores:

SELECT Student, Score, RANK() OVER (ORDER BY Score DESC) AS Rank
FROM Scores;

The result would be:

In this example, the RANK() function assigns a rank to each student based on their scores in descending order. Carol has the highest score and is ranked 1, while Bob and Dave have the same score and are both ranked 4, with no rank of 3 in between.

You can customize the ranking by changing the ORDER BY clause and partitioning the data using PARTITION BY for more advanced ranking scenarios.

Practical Usage of RANK() Function in SQL

Top-N Queries: 

You can use RANK() to retrieve the top N records based on a specific criteria, such as the highest sales, best-performing employees, or top-rated products.

SELECT name, sales, RANK() OVER (ORDER BY sales DESC) AS sales_rank
FROM sales_data
WHERE sales_rank <= 10;

This query retrieves the top 10 sales records.

Identifying Ties: 

RANK() helps identify tied values within a dataset. This can be useful in cases where you want to award prizes or bonuses to individuals with the same ranking.

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank
FROM exam_results;

You can then handle tied ranks accordingly in your application.

Pagination: 

When implementing pagination in web applications, you can use RANK() to retrieve a specific page of results. For example, if each page contains 20 records, you can fetch records 21 to 40 by using RANK().

SELECT name, score
FROM students
WHERE RANK() OVER (ORDER BY score DESC) BETWEEN 21 AND 40;

These practical examples demonstrate how the RANK() function can be a valuable tool for making data-driven decisions, presenting information to users, and gaining insights from your database.

Frequently Asked Questions

What are the limitations of rank order?

It is very challenging to compare one person to another and rank individuals when a large number of data are being evaluated. It cannot be determined how much better or worse one is than another or who is the best.

What distinguishes RANK and DENSE_RANK from one another?

The grades will be given the same rank by RANK and DENSE_RANK, relying on where they rank with respect to the other values. The next known ranking value will then be ignored by RANK, while DENSE_RANK will continue to use the following chronological ranking value.

What is difference between rank () ROW_NUMBER () and Dense_rank () in SQL?

In SQL, RANK() assigns ranks to rows with tied values, leaving gaps. ROW_NUMBER() assigns unique sequential numbers to rows. DENSE_RANK() assigns consecutive ranks without gaps for tied values.

What is rank () Dense_rank () in SQL?

In SQL, the RANK() and DENSE_RANK() functions assign a rank to each row in a result set based on a specified column's values. RANK() leaves gaps for tied values, while DENSE_RANK() assigns consecutive ranks.

Conclusion

As we have come to the end of this blog, let us see what we have discussed so far. In this blog, we have discussed the rank function in SQL. Then we discussed its types which are RANK(), ROW_NUMBER(), DENSE_RANK(), and NTILE. 

If you like to learn more, you can check out our articles: 

• Difference between RANK and DENSE_RANK
• TCL Commands in SQL
• Functions in SQL

 

Happy Learning, Ninja!