Row_Number function in SQL Server


Ranking functions provide a very good feature of assigning numbering to the records in the result set. Row_Number is one of these functions available in SQL server which allows us to assign rankings or numbering to the rows of the result set data. Different values are assigned to different rows, based on the type of ranking function used. They are basically of the following types :

  1. Row_Number
  2. Rank
  3. Dense_Rank
  4. NTile

In this article, we will be discussing the use of Row_Number function.

To understand this concept of ranking, we need to first understand the syntax of it. The syntax is :

Rank() Over ( {Partition_By_Clause} Order_By_Clause)

First of all, Rank() is the function to assign the numbering or ranking to the result set data. Next, the Over keyword is used along with the conditions specified in the ‘(‘ brackets (i.e. the Partition and Order by clause), to decide how the ranking or numbering is started and
implemented on the result set data. Here, these two keywords are also very important to be understood, before we move further.

Order_By_Clause : This clause decides how the ranking or numbering of the data starts. For example, if we specify Order By EmployeeID desc, it will result in ordering the data in descending order by EmployeeID and then assign the rank to the records, starting from 1 to n, based on the type of ranking function we use (i.e. rank, dense rank etc.)

Partition_By_Clause : This clause basically provides the grouping functionality on the result set data. When used in combination with the order by clause, it results in grouping the data and then the ranking of the rows is done, based on the type of ranking function we use (i.e. rank, dense rank etc.)

So its the over keyword, along with these two clauses, that handle the ranking process of the records, when using the ranking functions.

What is Row_Number function ?

This function works by assigning continuous ranking to the records, without skipping any number in the result set, whether it is partitioned or not. At the end of the discussion, we will see what we mean by continuous ranking and not skipping any record.

For this, we will create a table named DepartmentMembers, which stores names of the members, along with their DepartmentName. I know this is not a perfect database structure, but just a sample table with some records inserted into it, to discuss the concept. So our setup table will be like following :

Row_Number() in SQL

Next, we will be applying the Row_Number function with two different cases and will discuss them one by one. In first case, we will apply only the order by clause and in second case, we will add the partition by clause, along with the order by clause.

The main point here is that the order by clause is MUST for applying the ranking functions. So let’s start.

Case 1 – Ranking entire result set using Order By clause

Based on the syntax above and data created in the sample table, we will apply the Row_Number function now. So our query becomes :

SELECT ROW_NUMBER() OVER (ORDER BY DepartmentName DESC) AS GeneratedRank, MemberName, Gender, DepartmentName
FROM dbo.DepartmentMembers

Now execute this query and see the results.

Row_Number2

What this query has done is that, it selects the results from the table and order them by DepartmentName in descending order. Further, it generat the rank or numbering for the records as we can see the GeneratedRank column. So, simple numbering was generated for the records.

Case 2 – Ranking partitioned result set using Partition By Clause

 Next, we will add the Partition By clause to it. So the query now changes to :

SELECT ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY DepartmentName DESC) AS GeneratedRank, MemberName, Gender, DepartmentName
FROM dbo.DepartmentMembers

Now run the query and see the results.

Row_Number() in SQL

This time, the partition by clause grouped the results based on the Gender, ordered the data of the groups by DepartmentName in descending order and then finally applied the numbering or rank on the results, based on the ranking function used.

So in the example above, we can see that the ranking assigned to the records remains in continuous order, regardless of whether it was complete result set or partitioned result set.

Basically use of the order by clause only applies the numbering on the records or in other words, decides the sequence or starting point of the numbering. Further, partition clause along with the order by, groups the records first and further applies the numbering based on the order by clause. In next post, we will discuss the Rank() function.

Hope you enjoyed reading it…!!!

Advertisements

About Jasminder

.Net developer and blogger by profession, keen to learn new technologies, love nature, music and cricket.
This entry was posted in SQL Server and tagged . Bookmark the permalink.

I have a suggestion..

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s