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:

USE TEST
SELECT RANK() OVER(ORDER BY Class) AS 'Rank',
FName,Class, Salary
FROM Students
ORDER BY '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;

USE TEST
SELECT DENSE_RANK() OVER(ORDER BY Class) AS 'DenseRank',
FName,Class, Salary
FROM Students
ORDER BY '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-

USE TEST
SELECT NTILE(5) OVER(ORDER BY Salary) AS Percentile,
FName,Class, Salary
FROM Students
ORDER BY Percentile

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.

Hope you enjoyed, reading this stuff!

And also comments on this!!

Regards

Piyush Bajaj

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook