Windowing and Ranking in SQL Server – Part 3

Hello Folks,

You must have heard about this feature which was introduced earlier in SQL Server 2005. And also you had seen my previous article on Windowing as well as ROW_NUMBER. If you want to refer it again, so here’s the link;

This article post is mainly being based upon Ranking Functions like Rank, Dense_Rank, and Ntile.

RANK () functions:

The RANK functions return values as if rows were competing according to the windowed sort order.

The ties are being grouped together with the same ranked value.

It returns the rank of each row within the partition of a result set.

The syntax can be seen as-

RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

It can also be used with partitioned clause.

This will become much clear if you would see an example:

I have used Students table from TEST database, it can be seen as-

Since, we know there are three classes, and so if you want to rank according to the classes, the query can be written as follows:

Transact-SQL

1

2

3

4

5

USETEST

SELECTRANK()OVER(ORDERBYClass)AS'Rank',

FName,Class,Salary

FROMStudents

ORDERBY'Rank'

The result can be seen as:

DENSE_RANK () functions:

The DENSE_RANK () functions returns the rank of rows within the partition of a result set, without any gaps in the ranking.

Here, the tied rows only consume the single value in the ranking. So the next rank would be the next place in the ranking order.

It can also be used with partitioned clause.

For e.g.,

The following query shows that what happens with the DENSE_RANK () function;

Transact-SQL

1

2

3

4

5

USETEST

SELECTDENSE_RANK()OVER(ORDERBYClass)AS'DenseRank',

FName,Class,Salary

FROMStudents

ORDERBY'DenseRank'

The result can be seen as:

NTILE () functions:

The NTILE () function organizes the rows into ‘n’ number of groups, called tiles, and returns the tile number.

It distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

For e.g., if there are 74 rows and we have declare NTILE (10) for it. Then, the first 4 tiles get 8 rows each, and tiles 5 t0 10 get 7 rows each.

It works great for the larger data, but sometimes been skew for very smaller data sets in which the rows are even less than the tiles.

The common example of NTILE () is the percentile scoring used in college entrance exams.

For e.g.,

I have used the same “Students” table from “TEST” database, as shown above;

Now if there are 9 records, and I want to apply the NTILE () function, so it can be seen as-

Transact-SQL

1

2

3

4

5

USETEST

SELECTNTILE(5)OVER(ORDERBYSalary)ASPercentile,

FName,Class,Salary

FROMStudents

ORDERBYPercentile

The result can be seen as:

You can see that, since the Tile is 5, so it has distributed according to it.

Well this was all about Windowing and Ranking functions. So it comes to an end of this sequel.

Share This Story, Choose Your Platform!

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.