ROW_NUMBER, RANK, DENSE_RANK and NTILE

Leave a comment (0) Go to comments

In this article, we will discuss very simple but much required topic for daily use in SQL Server or for preparation for MCTS SQL Server 2008 Developer (70-451).  Let’s start one by one with these:

1) ROW_NUMBER: assigns numbers from 1 to N on the basis of sorting order specified by the user. ROW_NUMBER never accounts for ties in the result set. The rows with the same values within the column that is specified in order by, can produce different row numbering.

2) RANK: RANK can be same as ROW_NUMBER, provided no ties in the result set. However, if ties occur in result set, RANK will assign same value to each row and then assigns the next value, leaving a gap in t he sequence with respect to number of rows tied.

3) DENSE_RANK: It works same as RANK, but does not leave gap in the sequence with repect to the number of rows tied.

4) NTILE: It divides result set into equal number of groups. Suppose you have 54 values and you use NTILE(5); it will make 4 groups of 11 values and 1 group of 10 values.

Very short but very useful concept of SQL. It can gain you marks in your certification ;).

EOF - ROW_NUMBER, RANK, DENSE_RANK and NTILE, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

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


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.