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
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 * from dbo.UsersTable
–generate SP with NTILE which gives us results in 3 bunch
Create PROC SPUsersTableNTile (@RNT INT)
Select Userid,FirstName,LastName,JoiningDate from
Select UserID,FirstName,LastName,JoiningDate,NTILE(3) over(order by userID) RNT from dbo.UsersTable
) as t where
–let us check SP, whether it really works.
EXEC SPUsersTableNTile 1
EXEC SPUsersTableNTile 2
EXEC SPUsersTableNTile 3