SQL Server: Tips and Tricks - 2 (2017)
Filtering by OFFSET-FETCH Options in Select query
Scenario:
You are working as SQL Server developer with front end development team. The front end team needs to implement pagination. Confuse about Pagination? No problem. Think about viewing your bank statement or credit card statement. Where applications shows only 10 or 20 records per page and you have to click next to see next records. That is called pagination.
Now you understand pagination, the front end development needs SQL query from you that can be used to returned required results and they should be able to pass page number to return records.
Solution:
There are multiple ways to write Pagination queries, one of them is by using OFFSET FETCH clause. You have to sort the records if you want to use OFFSET FETCH.
Let's create dbo.TotalSale table and insert some sample records. I have only inserted 11 records.
CREATE TABLE [dbo].[TotalSale]
(
[id] [int] NOT NULL ,
[SalePersonFName] [varchar](100) NULL ,
[SalePersonLName] [varchar](100) NULL ,
[ProductName] [varchar](100) NULL ,
[ItemsSold] [int] NULL ,
[SoldPrice] [float] NULL ,
[SoldDate] [date] NULL ,
[City] [varchar](100) NULL ,
[State] [varchar](100) NULL ,
[Country] [varchar](100) NULL ,
[Region] [varchar](100) NULL
)
INSERT [dbo].[TotalSale]
( [id], [SalePersonFName], [SalePersonLName], [ProductName],
[ItemsSold], [SoldPrice], [SoldDate], [City], [State], [Country],
[Region] )
VALUES ( 1, N'Aamir', N'Shahzad', N'TV', 1, 700, CAST(N'2015-07-15' AS DATE),
N'Charlotte', N'NC', N'USA', N'North America' )
, ( 2, N'M', N'Raza', N'Cell Phone', 2, 800, CAST(N'2015-07-15' AS DATE),
N'Charlotte', N'NC', N'USA', N'North America' )
, ( 3, N'Christy', N'Ladson', N'TV', 3, 1600,
CAST(N'2015-04-02' AS DATE), N'High Point', N'NC', N'USA',
N'North America' )
, ( 4, N'John', N'Rivers', N'Laptop', 5, 2400,
CAST(N'2014-03-09' AS DATE), N'Jersey City', N'NJ', N'USA',
N'North America' )
, ( 5, N'Najaf', N'Ali', N'Computer', 1, 300,
CAST(N'2015-06-20' AS DATE), N'Karachi', N'Sindh', N'Pakistan',
N'Asia' )
, ( 6, N'Sukhjeet', N'Singh', N'TV', 2, 900, CAST(N'2015-06-21' AS DATE),
N'ChandiGar', N'Punjab', N'India', N'Asia' )
, ( 7, N'Chirag', N'Patel', N'Cell Phone', 5, 1500,
CAST(N'2015-06-23' AS DATE), N'AhmadAbad', N'Gujrat', N'India',
N'Asia' )
, ( 8, N'Aleena', N'Aman', N'Laptop', 2, 800,
CAST(N'2015-05-25' AS DATE), N'Lahore', N'Punjab', N'Pakistan',
N'Asia' )
, ( 9, N'Petra', N'Henry', N'TV', 10, 5000, CAST(N'2015-04-08' AS DATE),
N'Paris', N'Île-de-France', N'France', N'Europe' )
, ( 10, N'Rita', N'Roger', N'Laptop', 7, 2100,
CAST(N'2015-04-11' AS DATE), N'Paris', N'Île-de-France', N'France',
N'Europe' )
, ( 11, N'Tamara', N'Tony', N'Cell Phone', 2, 1200,
CAST(N'2015-03-03' AS DATE), N'Frankfurt', N'Hesse', N'Germany',
N'Europe' )
1) Lets say if we would like to skip first 5 rows and want to show all rest of the rows we can use below query.
Select
[id], [SalePersonFName], [SalePersonLName], [ProductName],
[ItemsSold], [SoldPrice]
from dbo.TotalSale
order by id
OFFSET 5 rows
|
|
2) Now if we would like to show 3 records per page, we can use below query. In this case we are going to show first page
Select
[id], [SalePersonFName], [SalePersonLName], [ProductName],
[ItemsSold], [SoldPrice]
from dbo.TotalSale
order by id
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
|
|
Noticed that I have OFFSET 0, that means that I want to show first page and with 3 rows. If I want to show second page records, I will set to OFFSET 1, the Next 3 ROWS ONLY part will stay the same as want to show only 3 rows per page.
We can use variables so we don't have to make changes in the query and by changing the value of variables, we can return our required results. You can create Stored Procedure if you like by using below query.
Declare @PageNumber int
Declare @RowsPerPage int
set @RowsPerPage=3
SET @PageNumber=1
Select
[id], [SalePersonFName], [SalePersonLName], [ProductName],
[ItemsSold], [SoldPrice]
from dbo.TotalSale
order by id
OFFSET (@PageNumber-1)*@RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
If we need to provide Stored Procedure to Front End team, which accepts page number and number of rows they would like to return for each page, you can use below to create Stored Procedure.
Create procedure dbo.sp_GetSaleRecordsPerPage
@PageNumber int,
@RowsPerPage int
AS
BEGIN
Select
[id], [SalePersonFName], [SalePersonLName], [ProductName],
[ItemsSold], [SoldPrice]
from dbo.TotalSale
order by id
OFFSET (@PageNumber-1)*@RowsPerPage
ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
END
Let's say if we would like to return second page with 4 records, we can use the dbo.sp_GetSaleRecordsPerPage by providing below parameter values.
EXEC dbo.sp_GetSaleRecordsPerPage 2,4
|
|
