O'Reilly logo
  • Dustin Waybright thinks this is interesting:

SELECT custid, orderid, val,
  ROW_NUMBER() OVER(ORDER BY val) AS rownum,
  RANK()       OVER(ORDER BY val) AS rnk,
  DENSE_RANK() OVER(ORDER BY val) AS densernk,
  NTILE(100)   OVER(ORDER BY val) AS ntile100
FROM Sales.OrderValues;


Cover of Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012


RANK allows values to tie, if 7 is 40 and the next two numbers are 41, then their rank is both 8. 9 is then skipped and 10 is displayed. With DENSE_RANK, all integers in the sequence are maintained and the next rank would be 9 instead of 10. NTILE generates the number of equally sized tiles as specified in the expression.