Tuesday, January 29, 2013

How to use rank function in SQL Server ?

Posted by Rahul Kharde at 9:38 PM
The ROW_NUMBER () function in SQL Server  returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.


The RANK() function in SQL Server returns the position of a value within the partition of a result set, with gaps in the ranking where there are ties.


The DENSE_RANK() function in SQL Server returns the position of a value within the partition of a result set, leaving no gaps in the ranking where there are ties.


The NTILE() function in SQL Server return distributes the rows in an ordered partition into a specified number of groups.

Example
CREATE TABLE Students(
      Stud_ID     INT IDENTITY(1,1),
      Stud_Name   VARCHAR(100),
      Stud_Mark   INT
)

INSERT INTO Students(Stud_Name,Stud_Mark)
VALUES('a',60),
('b',94),
('c',70),
('d',63),
('e',60),
('f',60),
('g',94),
('h',47),
('i',70),
('j',60)


SELECT Stud_ID,Stud_Name,Stud_Mark,
      ROW_NUMBER() OVER (ORDER By Stud_Mark) as 'ROW NUMBER',
      RANK()                   OVER (ORDER By Stud_Mark) as 'RANK',
      DENSE_RANK()  OVER (ORDER By Stud_Mark) as 'DENSE RANK',
      NTILE(2)                 OVER (ORDER By Stud_Mark) as 'NTILE'
FROM Students




If you enjoyed this post and wish to be informed whenever a new post is published, then make sure you subscribe to my regular Email Updates. Subscribe Now!


Kindly Bookmark and Share it:

YOUR ADSENSE CODE GOES HERE

0 comments:

Have any question? Feel Free To Post Below:

 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top