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

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

Paging Stored Procedure in SQL Server 2008/2005

Paging is something every website developer need. To make paging happens, you need to work a lot if you create your logic in any front end but it became very easy if you will use windows partition functions of SQL Server. My previous article was completely dedicated to this topic and in this article, I am going to show you how you can create paging with the help of SQL Server’s Row_Number() function, if you don’t have identity field.
Generally people used to get full record sets in dataset (.NET) and used to show only those records which needs to display, I don’t encourage this practice, fetch only those records from database which needs to display, by this way, you can reduce network traffic and can get good performance.
Anyway, let me show you the magical, handy stored procedure.
Create Table UsersTable
(
      UserID Varchar(25),
      FirstName Varchar(25),
      LastName Varchar(25),
      JoiningDate datetime
)
GO


Insert into 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 UsersTable
go


–creating stored procedure for searching user with paging.
CREATE PROC SearchUser
@PageNum INT, –pass page number you want to see
@UserPerPage INT, –pass number of users, you want to see on one page.
@Date Datetime –you can pass as many varialbe as you want to put in WHERE clause.
AS
BEGIN
      Select * FROM
      (
            SELECT
                  UserID,FirstName,LastName,JoiningDate,ROW_NUMBER() Over(Order by UserID) as rowNum
            FROM
                  UsersTable
            WHERE
                  JoiningDate<=@Date
      ) as t WHERE rowNum BETWEEN (@PageNum1) * @UserPerPage+1 AND @PageNum * @UserPerPage
END
GO


–check whether paging actually works or not
EXEC SearchUser 1,2,‘20090920’
GO


EXEC SearchUser 2,2,‘20090920’
GO


EXEC SearchUser 3,2,‘20090920’
GO
This is just a small demonstration about what you can do, you can make this SP more friendly and customize as per your need.

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

Paging Stored Procedure in SQL Server 2008/2005

Paging is something every website developer need. To make paging happens, you need to work a lot if you create your logic in any front end but it became very easy if you will use windows partition functions of SQL Server. My previous article was completely dedicated to this topic and in this article, I am going to show you how you can create paging with the help of SQL Server’s Row_Number() function, if you don’t have identity field.
Generally people used to get full record sets in dataset (.NET) and used to show only those records which needs to display, I don’t encourage this practice, fetch only those records from database which needs to display, by this way, you can reduce network traffic and can get good performance.
Anyway, let me show you the magical, handy stored procedure.
Create Table UsersTable
(
      UserID Varchar(25),
      FirstName Varchar(25),
      LastName Varchar(25),
      JoiningDate datetime
)
GO


Insert into 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 UsersTable
go


–creating stored procedure for searching user with paging.
CREATE PROC SearchUser
@PageNum INT, –pass page number you want to see
@UserPerPage INT, –pass number of users, you want to see on one page.
@Date Datetime –you can pass as many varialbe as you want to put in WHERE clause.
AS
BEGIN
      Select * FROM
      (
            SELECT
                  UserID,FirstName,LastName,JoiningDate,ROW_NUMBER() Over(Order by UserID) as rowNum
            FROM
                  UsersTable
            WHERE
                  JoiningDate<=@Date
      ) as t WHERE rowNum BETWEEN (@PageNum1) * @UserPerPage+1 AND @PageNum * @UserPerPage
END
GO


–check whether paging actually works or not
EXEC SearchUser 1,2,‘20090920’
GO


EXEC SearchUser 2,2,‘20090920’
GO


EXEC SearchUser 3,2,‘20090920’
GO
This is just a small demonstration about what you can do, you can make this SP more friendly and customize as per your need.

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

Windowing Partition Functions (Rank(), Row_Number() and Dense_Rank())

Microsoft SQL Server 2005 comes up with fabulous Partitioning functions which work well in SQL Server 2008 too. Just because of ignorance or unawareness of these functions, programmer used to iterate BIG BIG loops in front-end. Without much a boring lecture, let me start over functions now.
Row_Number():  Basically Row_Number() function is used to give number to each row in result set. It plays an important and a crucial role in many difficult situations. One can use it for paging purpose too (I probably will post one SP in future article which you can directly use in your front-end for paging purpose).
–create temp table for demo
IF OBJECT_ID(‘tempdb..#Employee’) is not null DROP TABLE #EMPLOYEE
Create Table #Employee
(
      FirstName varchar(20),
      LastName Varchar(20),
      DepartMent varchar(20)
)

–insert few records
Insert Into #Employee
SELECT ‘Ritesh’,‘Shah’, ‘MIS’ UNION ALL
SELECT ‘Rajan’,‘Shah’,‘ACCT’ UNION ALL
SELECT ‘Rajan’,‘Mehta’,‘ACCT’ UNION ALL
SELECT ‘Alka’,‘Shah’,‘MIS’

–simple Row_Number with Order By First Name, Last Name
–this will not make any partition and simply give row number to every row
SELECT ROW_NUMBER() over(order by FirstName,LastName) as Num,FirstName,LastName,DepartMent
FROM #Employee

–this will make a partition on First Name
–so, every first instance of FirstName will have row number 1
–you can find duplicate records with this way too. 🙂
SELECT ROW_NUMBER() over(Partition by FirstName order by FirstName,LastName) as Num,FirstName,LastName,DepartMent
FROM #Employee
Rank() and Dense_Rank(): These two functions mainly used to use for giving Rank to each row.  You may use it for finding Toppers based on examination results records set or maybe use it for finding few top vendors based on track records of sales you have etc. There is only one small but technically big difference between Rank() and Dense_Rank() functions which I am going to show you in practical script below which will be easy to evaluate as I am going to show you all possible partitioning function in one T-SQL.
–create temp table for demo
IF OBJECT_ID(‘tempdb..#SampleOrder’) is not null DROP TABLE #SampleOrder
Create Table #SampleOrder
(
      OrderID Int Identity(1,1),
      ClientID int,
      TotalSample int,
      SampleDate datetime
)
–insert few records
Insert Into #SampleOrder
SELECT 1,2,GETDATE()-5 UNION ALL
SELECT 2,5,GETDATE()-8 UNION ALL
SELECT 1,22,GETDATE()-3 UNION ALL
SELECT 3,2,GETDATE()-1 UNION ALL
SELECT 1,2,GETDATE()-5
SELECT *,
            ROW_NUMBER() over(order by TotalSample) as RowNum,
            ROW_NUMBER() over(Partition By ClientID order by TotalSample) as RowNumP,
            Rank() over(order by TotalSample) as Ran,
            Rank() over(Partition By ClientID order by TotalSample) as RanP,
            Dense_Rank() over(order by TotalSample) as DRan,
            Dense_Rank() over(Partition By ClientID order by TotalSample) as DRanP
FROM #SampleOrder

If you will observe output of above T-SQL, you will get to know the difference between Rank and Dense_Rank. There is only difference, if you will get same instance based on partition, rank will give same number all, suppose we get three same instance (same clientID three time with same value) rank will give it, suppose 1 for all three and when next instance come, rank will give it 4 rather than 2. In Dense_Rank, you will get 2, it won’t break the chain.
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 of
 http://www.SQLHub.com

Windowing Partition Functions (Rank(), Row_Number() and Dense_Rank())

Microsoft SQL Server 2005 comes up with fabulous Partitioning functions which work well in SQL Server 2008 too. Just because of ignorance or unawareness of these functions, programmer used to iterate BIG BIG loops in front-end. Without much a boring lecture, let me start over functions now.
Row_Number():  Basically Row_Number() function is used to give number to each row in result set. It plays an important and a crucial role in many difficult situations. One can use it for paging purpose too (I probably will post one SP in future article which you can directly use in your front-end for paging purpose).
–create temp table for demo
IF OBJECT_ID(‘tempdb..#Employee’) is not null DROP TABLE #EMPLOYEE
Create Table #Employee
(
      FirstName varchar(20),
      LastName Varchar(20),
      DepartMent varchar(20)
)

–insert few records
Insert Into #Employee
SELECT ‘Ritesh’,‘Shah’, ‘MIS’ UNION ALL
SELECT ‘Rajan’,‘Shah’,‘ACCT’ UNION ALL
SELECT ‘Rajan’,‘Mehta’,‘ACCT’ UNION ALL
SELECT ‘Alka’,‘Shah’,‘MIS’

–simple Row_Number with Order By First Name, Last Name
–this will not make any partition and simply give row number to every row
SELECT ROW_NUMBER() over(order by FirstName,LastName) as Num,FirstName,LastName,DepartMent
FROM #Employee

–this will make a partition on First Name
–so, every first instance of FirstName will have row number 1
–you can find duplicate records with this way too. 🙂
SELECT ROW_NUMBER() over(Partition by FirstName order by FirstName,LastName) as Num,FirstName,LastName,DepartMent
FROM #Employee
Rank() and Dense_Rank(): These two functions mainly used to use for giving Rank to each row.  You may use it for finding Toppers based on examination results records set or maybe use it for finding few top vendors based on track records of sales you have etc. There is only one small but technically big difference between Rank() and Dense_Rank() functions which I am going to show you in practical script below which will be easy to evaluate as I am going to show you all possible partitioning function in one T-SQL.
–create temp table for demo
IF OBJECT_ID(‘tempdb..#SampleOrder’) is not null DROP TABLE #SampleOrder
Create Table #SampleOrder
(
      OrderID Int Identity(1,1),
      ClientID int,
      TotalSample int,
      SampleDate datetime
)
–insert few records
Insert Into #SampleOrder
SELECT 1,2,GETDATE()-5 UNION ALL
SELECT 2,5,GETDATE()-8 UNION ALL
SELECT 1,22,GETDATE()-3 UNION ALL
SELECT 3,2,GETDATE()-1 UNION ALL
SELECT 1,2,GETDATE()-5
SELECT *,
            ROW_NUMBER() over(order by TotalSample) as RowNum,
            ROW_NUMBER() over(Partition By ClientID order by TotalSample) as RowNumP,
            Rank() over(order by TotalSample) as Ran,
            Rank() over(Partition By ClientID order by TotalSample) as RanP,
            Dense_Rank() over(order by TotalSample) as DRan,
            Dense_Rank() over(Partition By ClientID order by TotalSample) as DRanP
FROM #SampleOrder

If you will observe output of above T-SQL, you will get to know the difference between Rank and Dense_Rank. There is only difference, if you will get same instance based on partition, rank will give same number all, suppose we get three same instance (same clientID three time with same value) rank will give it, suppose 1 for all three and when next instance come, rank will give it 4 rather than 2. In Dense_Rank, you will get 2, it won’t break the chain.
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 of
 http://www.SQLHub.com