Apply ranking functions

May include but is not limited to: RANK, PARTITION BY, DENSE_RANK, OVER, ROW_NUMBER, NTILE

When you need to have your result sets numbered, ranked, partitioned by some custom logic, you should turn your attention towards ranking functions (also known as windowing functions). Luckily, there are only a few of them, and you can implement some good behaviors (such as paging) with the help of them. Let’s see the list:

  • ROW_NUMBER: is an ideal candidate for paging data. It takes the OVER clause with an ORDER BY clause, and an optional PARTITION BY. For example: SELECT ROW_NUMBER() OVER(ORDER BY LastName) FROM Employees. When you use PARTITION BY (which must take place within the OVER clause, preceding ORDER BY), you can partition your result by the column name you’ve specified. This means that after every new value, the numbering starts again.
  • RANK: the syntax is the same as was with ROW_NUMBER, but RANK, as the name shows, ranks the results by the specified ORDER BY value. When duplicates are found, it assigns the same number to the other values to them, but skips the next value.
  • DENSE_RANK: the same as RANK, but without skipping any rank number.
  • NTILE: divides the result set into approximate n-tiles, such as quintiles, percentiles, etc. If you have ever stumbled into statistics, you should be quite familiar with these quintiles or deciles. A good example on NTILE is to compute who are the richest in a set.
Advertisements