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;
- 5. Grouping and Windowing
- from Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012
- Publisher: Microsoft Press
- Released: December 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.
Share this highlighthttp://www.safaribooksonline.com/a/training-kit-exam/61502/