Warning: is_writable() [function.is-writable]: open_basedir restriction in effect. File(/f5/sqllion/public/wp-content/uploads/shareaholic/) is not within the allowed path(s): (/fs3d/sqllion/:/nfsn/apps/php53/lib/php/:/nfsn/apps/php5/lib/php/:/nfsn/apps/php/lib/php/) in /fs3d/sqllion/public/wp-content/plugins/sexybookmarks/sexy-bookmarks.php on line 335
T-SQL RowNumber() vs Rank() vs DenseRank() vs NTILE() | SQL Lion
Home > T - SQL > T-SQL RowNumber() vs Rank() vs DenseRank() vs NTILE()

T-SQL RowNumber() vs Rank() vs DenseRank() vs NTILE()

How if we found some things in life that we want to be done so smoothly, so perfect without any flaw or fracas that something like a wizard or some magic that makes things simpler and fine.

SQL Server comes with some magic words like the above phrase that makes simple the life of a developer or DBA to a great extent. And these magic words are RowNumber(), Rank(), DenseRank() and NTile().




We can do a lot more than what the words means by itself like starting from removing duplicates from a database, or getting the highest or second highest salaried employees in the organization, generating on the fly sequence number or identity column, or simply rank any column on the basis of its values, etc. The ranking functions set a rank to each of the records as per the condition specified. All these ranking function has its own meaning and purpose.

Let’s have a table to use to explain the effects with example. Let’s consider the dbo.employee table as below:

DeptNo empname salary

101

Ram

5000

102

Abhishek

7500

101

kumar

5000

104

prasad

6570

102

Jumla

7500

101

Harkesh

12000

101

John

4000

Run the below query to create the table in your database and to insert data into it.

CREATE TABLE [dbo].[employee](
	[DeptNo] [int] NULL,
	[empname] [varchar](50) NULL,
	[salary] [float] NULL
)
INSERT INTO [dbo].[employee]([DeptNo],[empname],[salary])
select '101', 'Ram', '5000' Union All
select '102', 'Abhishek', '7500' Union All
select '101', 'kumar', '5000' Union All
select '104', 'prasad', '6570' Union All
select '102', 'Jumla', '7500' Union All
select '101', 'Harkesh', '12000' Union All
select '101', 'John', '4000'

Here in this article, I will elaborate each of these functions in brief and with examples.

Each of these functions has a similar type of syntax in T – SQL.

[ROWNUMBER( )/RANK()/DENSERANK()/NTILE()] OVER ( [ < partition_by_clause > ] < order_by_clause > )

Here <partition by clause> is optional, but is very useful when dealing with complex logic. It will partition the result set into sub sets on which the ranking functions should work. The ranking functions (windowed functions) will work on each partition separately. If we don’t specify the partition by clause, the whole result set will be considered as single partition.

When we use “Partition by “, we need to specify the columns on which it will partition the data.

For example, in the above dbo.employee table, if we want to partition by DeptNo column, then the employee table will be divided into 3 partitions as shown below.

Employee table partitioned by DeptID

Now the ranking functions / windowed functions will affect each of these partitions separately.

<Over> Clause: This will prepare the result set before the associated ranking function or windowed function is applied i.e. like partitioning dataset or ordering (order by clause) it on certain criteria.

Let’s check each of the ranking functions one by one.

Click to get info on ROW_NUMBER()  Click to get info on RANK()  Click to get info on DENSE_RANK()  Click to get info on NTILE()




Categories: T - SQL
  1. No comments yet.
  1. No trackbacks yet.
You must be logged in to post a comment.