SQL Rank

The SQL Rank function was introduced in SQL Server 2005 and is part of a family of ranking functions. In order to explain the SQL Rank function, we need to look at it in context with the other rank functions.

  • RANK
  • DENSE_RANK
  • ROW_NUMBER
  • NTILE

This list may seem overwhelming, however most of the ranking functions are rather similar.

First, the syntax. Each ranking function takes a mandatory parameter of a column or value used in order to “rank”. Let’s look at an example run against the adventureworks light database.

[cc lang=”sql”]
SELECT
UnitPrice = UnitPrice
,RowNumber = ROW_NUMBER() OVER (ORDER BY UnitPrice)
,Rank = RANK() OVER (ORDER BY UnitPrice)
,DenseRank = DENSE_RANK() OVER (ORDER BY UnitPrice)
,NTile = NTile(100) over (ORDER BY UnitPrice)
FROM SalesLT.SalesOrderDetail
[/cc]

Looking at the results of the query provides the best explanation for the functions.

Results of SQL Rank Functions

Results of SQL Rank Functions

From this list we can compare the UnitPrice in order to determine what each function is doing.

  1. The rownumber function, simply add one number per each row that is returned.
  2. The Rank function will compare the unit price and provide an integer based on it’s comparison with the previous row. If they are equal it will return the same rank. Internally, the rank will be incremented by one for each row (whether it is equal or not) and the next non-equal value will display the next internal rank. (I know this is a tough sentence, just look at the example).
  3. Dense_Rank performs the same as rank, except it does not keep an internal increment and instead simply increments the count by one when the value is not equal
  4. Ntile is a beast of it’s own. It simply takes the entire result set, and divides it equally into the number of segments specified in the parameter. In this case 100.

3 comments
Jacob 29 Aug 2015 at 6:32 am

请教一下:如果我想测试 一个trigger 里的每行的执行效率。这个trigger又调用了一个procedure过程,那么我在测试 时使用BEGIN DBMS_PROFILER.START_PROFILER( any comment’); inesrt into t_1 values(1,2) ; 这样inesrt的语句,触发上面的trigger DBMS_PROFILER.STOP_PROFILER;END;--可是出来的html报告只是包含你调用的引用的的哪个触发器和过程。并没有列出里面触发器里每一行的代码。不知道这样的结果是不是正常的,还是有别的方法可以看到trigger和procedure里每行的执行时间谢谢!

Umm-e-Habiba Siddiqui 04 Sep 2013 at 8:09 am

its really helpful, good work but Ntile’s explanation isn’t sounds understanding, btw thanks! (Y)

Paulo 29 Aug 2015 at 8:07 am

Hi,Great video! Your services too are rlealy great! You provide a nice range of services and I’m sure many people could use your services. I have used you in the past, the work was done quickly and efficiently and still done to an escellent level of expertise! For the price, this is an amazing offer as the work you did was worth so much more. I would highly recommend this service to anyone with computing needs. Check out their main site for more info milesconsultingcorp . com.

Featured Articles

 Site Author

  • Thanks for visiting!