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-

1_SQL_Server_Windowing_and_Ranking_Part3

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:

2_SQL_Server_Windowing_and_Ranking_Part3

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:

3_SQL_Server_Windowing_and_Ranking_Part3

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:

4_SQL_Server_Windowing_and_Ranking_Part3

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

 
Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Piyush Bajaj

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”.

View all posts by Piyush Bajaj →

Leave a Reply

Your email address will not be published. Required fields are marked *