RANK() Function in SQL Server

The RANK() function in SQL is a window function (functions that process a collection of rows and yield an individual aggregated result for each row) in SQL that assigns a rank based on the specified attribute(s) to each row within a partition of a result set.

SQL RANK Functions

Here's an overview of four rank function in sql:

These functions are invaluable in data analysis, allowing for complex sorting, ranking, and distribution of data within SQL queries.

Before we begin with the rank function, let's define some sample data to work upon. For this sample data, we will create a table name STUDENT with the following attributes -

• NAME
• SUBJECT
• MARKS

Creating Table -

Inserting Data -

Now our table looks like this -

ROW_NUMBER() SQL RANK Function

The ROW_NUMBER() rank function in SQL returns a unique sequential number for each row in the data. It gives the first rank to the first row, the second rank to the second row, and so on.

The ROW_NUMBER() function assigns a different rank to rows with similar values.

The below-given syntax demonstrates the use of the ROW_NUMBER() function in the SQL server -

The following query assigns a rank to rows based on their marks irrespective of the subject in the descending order $i.e.$ row with higher marks has a lower rank.

Output:

As you can see in the output, all rows have been assigned a unique rank. Even two people having the same marks $i.e.$, 74 have been assigned a different rank.

RANK() SQL RANK Function

RANK() rank function in SQL is used to specify the rank for each record in the result. As in the sample data, we have multiple subjects, and if we want to rank students based on their marks subject-wise, we will do the following things.

• We will use the PARTITION BY clause on SUBJECT.
• Each subset will get ranked as per the student's marks (in descending order).
• The result set uses the ORDER BY clause to display the result sorted according to their RANK.

The below-given syntax demonstrates the use of the RANK() function in the SQL server -

The following query shows the use of the RANK() function.

Output:

In the above output, it can be noticed that ranks have been awarded subject-wise $i.e.$ for any particular subject (say sub). Ranks have been assigned in the range of [1, n] where n is the number of students enrolled in n.

DENSE_RANK() SQL RANK Function

The DENSE_RANK() function in SQL assigns rank without any kind of gaps. Let us understand this by an example. If two rows have similar values and they have been assigned a rank x, then the next row will always be assigned with the rank x+1 and not x+2.

The below-given syntax demonstrates the use of the DENSE_RANK() function in the SQL server -

The following query shows the use of the DENSE_RANK() function.

Output:

As can be seen in the output, row number 5 and 6 (from the top) have been assigned with rank 5 but row number 7 have been assigned with rank 6.

If we had used the RANK() function, row number 7 would have been assigned with rank 7.

NTILE(N) RANK Function in SQL

The NTILE rank function in SQL is used to divide rows of a partition into $n$ number of pre-defined approximately equal groups. It assigns a bucket number to every row in a group to represent the group to which it belongs.

The below-given syntax demonstrates the use of the NTILE(N) function in SQL server -

The following query is applied to our Sample Data to split rows into three groups.

Output: