Result set in bunch with NTILE in SQL Server 2008 (Ranking Rows in Groups)

Well, We have just seen Paging Stored Procedure with the help of Windowing Function Row_Number () in my previous article. This is now time to look at one of the very handy yet useful function NTILE. This is really very useful function if you want to groups up your result sets in Microsoft SQL Server 2005/2008. If you want to get top 10% students based on the percentage they get, this becomes really very easy with NTILE function.

Actually I have used this function so many times but when I have used this function recently in one of my recent project, It pops up in my mind why not write something about this function in blog?

Actually I had result sets which I wanted to show in three bunch, this could be done via Row_number function as I wrote Paging stored procedure recently but since I have fixed requirement that no matters how many rows in total result sets, I wanted to display it in three bunch, I felt NTILE function most prominent candidate for this. Let us look at one small demonstration about this.

If OBJECT_ID('dbo.UsersTable') is not null Drop Table dbo.UserTable

Create Table dbo.UsersTable

(

      UserID Varchar(25),

      FirstName Varchar(25),

      LastName Varchar(25),

      JoiningDate datetime

)

GO

Insert into dbo.UsersTable

select 'Ritesh','Ritesh','Shah',GETDATE()-50 union all

select 'Rajan','Rajan','Shah',GETDATE()-4 union all

select 'Bihag','Bihag','Thaker',GETDATE()-10 union all

select 'Alka','Alka','Shah',GETDATE()-30 union all

select 'Rushik','Rushik','Shah',GETDATE()-25 union all

select 'Krunal','Krunal','Shah',GETDATE()-20 union all

select 'Bhaumik','Bhaumik','Shah',GETDATE()-10

GO

select * from dbo.UsersTable

go

--generate SP with NTILE which gives us results in 3 bunch

Create PROC SPUsersTableNTile (@RNT INT)

as

BEGIN

      Select Userid,FirstName,LastName,JoiningDate from

      (

            Select UserID,FirstName,LastName,JoiningDate,NTILE(3) over(order by userID) RNT from dbo.UsersTable

      ) as t where

      RNT=@RNT   

END

--let us check SP, whether it really works.

EXEC SPUsersTableNTile 1

EXEC SPUsersTableNTile 2

EXEC SPUsersTableNTile 3

Happy Coding!!!!

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com