This article explains how you can generate sequence numbers in SQL select query. It uses sql functions Row_Number, Rank and Dense_rank

The Rank function can be used to generate sequential number for each row, or to give a rank based on a specific criteria. Ranking function returns a ranking value for each row. However based on criteria more than one row can get the same rank.

There are 3 types of functions which can be used to rank the records.

- Row_number
- Rank
- Dense_rank

All these function makes use of OVER clause. The OVER clause determines the sequence in which rows are assigned a rank, it may be ascending or descending.

To properly view the functionality of these Ranking functions first we will create a table named StudentTotalMarks. To create this table use the below given query in Sql Server.

CREATE TABLE StudentTotalMarks ( StudentName VARCHAR(80), MarksObtained INT, TotalMarks INT )

Now we will insert the data into this table once created, with the insert command.

INSERT INTO StudentTotalMarks VALUES ('Tim Paul', 476, 500) INSERT INTO StudentTotalMarks VALUES ('Branda Johnson', 450, 500) INSERT INTO StudentTotalMarks VALUES ('Kate Berg', 356, 500) INSERT INTO StudentTotalMarks VALUES ('John Sean', 476, 500) INSERT INTO StudentTotalMarks VALUES ('Petro Smith', 450, 500) INSERT INTO StudentTotalMarks VALUES ('Suzie Brown', 450, 500) INSERT INTO StudentTotalMarks VALUES ('Rebecca Wilson', 489, 500) INSERT INTO StudentTotalMarks VALUES ('Charles Davis', 476, 500) INSERT INTO StudentTotalMarks VALUES ('Anna Taylor', 390, 500) INSERT INTO StudentTotalMarks VALUES ('Sean Jones', 455, 500) INSERT INTO StudentTotalMarks VALUES ('Mike Jackson', 356, 500)

These are the marks of 11 students, which are shown in the table below.

Now our aim is to Rank all the students on the basis of their MarksObtained. We can do this by Row_Number, Rank or Dense_Rank functions. Let me show you the working of all 3 of these functions.

**Row_Number**This function will rank the column sequentially. Starting with 1 and then incrementing by 1 (eg.1,2,3,4), regardless of the same data in the column to be ranked.

The following query displays the sequential number on a column by using Row_Number function.

Select StudentName,MarksObtained, ROW_NUMBER() OVER(ORDER BY MarksObtained DESC) AS Rank FROM StudentTotalMarks

The following output shows the data retrieved from the query.

### Rank

This function will rank the column based on the specified criteria. If the table two student have same marks then this function will give them same rank, however the next rank number will be skipped (eg.1,2,3,3,5,6) .

The following query displays the working of Rank function.

SELECT StudentName, MarksObtained, RANK() Over(ORDER BY MarksObtained DESC) AS Rank FROM StudentTotalMarks

The following output shows the data retrieved from the query.

Here three students got the same rank 2, so the next rank is 5 omitting 3 and 4 rank numbers.

### Dense_Rank

The Dense_rank() function is used where consecutive ranking values need to be given based on specific criteria. It Ranks the column in the same way as of Rank() function however rank is not skipped here (eg. 1,2,3,4,5,5,6)

To view the functioning of the Dense_rank, write down the following query.

SELECT StudentName, MarksObtained, DENSE_RANK() OVER(ORDER BY MarksObtained DESC) AS Rank FROM StudentTotalMarks

The following output shows the data retrieved from the query.

Here three students got same rank 2, so next rank is 3. here it is not 5 as it was in Rank() function.

You can also use those functions with Inner Join or Outer Join OR Cross Apply.

Below code uses Rank with Join

SELECT StudentName, MarksObtained, c.CourseID, c.CourseName RANK() Over(ORDER BY MarksObtained DESC) AS Rank FROM StudentTotalMarks S JOIN CourseDetails C ON C.CourseID = S.CourseID

by
rahul
at
8/23/2018 7:06:00 AM
Here three students got the same student name, so the next rank is 1 omitting 2 and 3 rank numbers |

Speak your mind :