Pagination example

1. Paginating All Orders

SQL Server Query 1

            
 -- Parameters for pagination  

 DECLARE @PageNumber INT = 3;  

 DECLARE @PageSize INT = 10;  

   

 -- Calculate the number of rows to skip  

 DECLARE @OffsetRows INT = (@PageNumber - 1) * @PageSize;  

   

 SELECT  

     OrderID,  

     CustomerID,  

     EmployeeID,  

     OrderDate,  

     RequiredDate,  

     ShippedDate,  

     Freight  

 FROM  

     Orders  

 ORDER BY  

     OrderDate DESC, -- Sort by OrderDate (descending)  

     OrderID DESC    -- Add a tie-breaker (e.g., primary key) for consistent results  

 OFFSET @OffsetRows ROWS  

 FETCH NEXT @PageSize ROWS ONLY;  

   

   

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Columns("OrderID","CustomerID","EmployeeID","OrderDate","RequiredDate","ShippedDate","Freight")  

     .From("Orders")  

     .OrderBy(new OrderBy().SetColumnDescending("OrderDate")  

         .SetColumnDescending("OrderID").Offset(20, 10))  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT OrderID,
       CustomerID,
       EmployeeID,
       OrderDate,
       RequiredDate,
       ShippedDate,
       Freight
FROM Orders
ORDER BY OrderDate DESC, OrderID DESC
OFFSET @pMAIN_2507200137022163010 ROWS FETCH NEXT @pMAIN_2507200137022163011 ROWS ONLY;


            
        

Parameters (If used)

Query Results 1:

2. Paginating Orders for a Specific Customer

SQL Server Query 2

            
 DECLARE @PageNumber INT = 2;  

 DECLARE @PageSize INT = 5;  

 DECLARE @CustomerID NCHAR(5) = 'ALFKI';  

   

 -- Calculate the number of rows to skip  

 DECLARE @OffsetRows INT = (@PageNumber - 1) * @PageSize;  

   

 SELECT  

     OrderID,  

     CustomerID,  

     OrderDate,  

     RequiredDate,  

     ShippedDate,  

     Freight  

 FROM  

     Orders  

 WHERE  

     CustomerID = @CustomerID -- Filter for a specific customer  

 ORDER BY  

     OrderDate ASC, -- Sort by OrderDate (ascending)  

     OrderID ASC    -- Tie-breaker  

 OFFSET @OffsetRows ROWS  

 FETCH NEXT @PageSize ROWS ONLY;  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select()  

     .Columns("OrderID","CustomerID","OrderDate","RequiredDate","ShippedDate","Freight")  

     .From("Orders")  

     .Where(new Where(new Column("CustomerID").Equale("ALFKI")))  

     .OrderBy(new OrderBy().SetColumnAscending("OrderDate")  

         .SetColumnAscending("OrderID").Offset(5, 5,))  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT OrderID,
       CustomerID,
       OrderDate,
       RequiredDate,
       ShippedDate,
       Freight
FROM Orders
WHERE CustomerID = @pMAIN_2507200137022243110
ORDER BY OrderDate ASC, OrderID ASC
OFFSET @pMAIN_2507200137022243111 ROWS FETCH NEXT @pMAIN_2507200137022243112 ROWS ONLY;


            
        

Parameters (If used)

Query Results 2:

3. Paginating with Joins and Aggregates

SQL Server Query 3

            
 DECLARE @PageNumber INT = 1;  

 DECLARE @PageSize INT = 5;  

   

 -- Calculate the number of rows to skip  

 DECLARE @OffsetRows INT = (@PageNumber - 1) * @PageSize;  

   

 SELECT  

     C.CompanyName,  

     C.ContactName,  

     COUNT(O.OrderID) AS TotalOrders  

 FROM  

     Customers AS C  

 LEFT JOIN  

     Orders AS O ON C.CustomerID = O.CustomerID  

 GROUP BY  

     C.CompanyName,  

     C.ContactName -- All non-aggregated columns in SELECT must be in GROUP BY  

 ORDER BY  

     C.CompanyName ASC, -- Sort by CompanyName  

     C.ContactName ASC  -- Tie-breaker for consistent sorting  

 OFFSET @OffsetRows ROWS  

 FETCH NEXT @PageSize ROWS ONLY;  

            
        

Create SQL query with SqlQueryBuilder 3

            
 var (sql3, parameters3) = new SqlQueryBuilder()  

     .Select()  

     .Column("C.CompanyName", "C.ContactName")  

     .Column(new COUNT(new Column("O.OrderID")), "TotalOrders")  

     .From("Customers", "C")  

     .Join(new List<IJoin>()  

     {  

         new LEFTJOIN().TableName("Orders", "O")  

             .On(new Column("C.CustomerID").Equale(new Column("O.CustomerID")))  

     })  

     .GroupBy(new GroupBy("C.CompanyName", "C.ContactName"))  

     .OrderBy(new OrderBy().SetColumnAscending("C.CompanyName")  

         .SetColumnAscending("C.ContactName").Offset(0, 5))  

     .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT C.CompanyName,
       C.ContactName,
       COUNT(O.OrderID) AS TotalOrders
FROM Customers AS C
     LEFT OUTER JOIN
     Orders AS O
     ON C.CustomerID = O.CustomerID
GROUP BY C.CompanyName, C.ContactName
ORDER BY C.CompanyName ASC, C.ContactName ASC
OFFSET @pMAIN_2507200137022283440 ROWS FETCH NEXT @pMAIN_2507200137022283441 ROWS ONLY;


            
        

Parameters (If used)

Query Results 3:

4. Top-Selling Products by Category, Filtered by Date & Minimum Sales

SQL Server Query 4

            
 -- Parameters for pagination and filtering  

 DECLARE @PageNumber INT = 2; -- Example: Get the 2nd page  

 DECLARE @PageSize INT = 5;   -- Example: 5 products per page  

 DECLARE @TargetYear INT = 1997; -- Example: Orders from 1997  

 DECLARE @MinCategorySalesValue DECIMAL(18, 2) = 50.00; -- Example: Minimum total sales for a category  

   

 -- Calculate the number of rows to skip  

 DECLARE @OffsetRows INT = (@PageNumber - 1) * @PageSize;  

   

 SELECT  

     Cat.CategoryName,  

     P.ProductName,  

     SUM(OD.Quantity * OD.UnitPrice * (1 - OD.Discount)) AS ProductTotalSales,  

     COUNT(DISTINCT O.OrderID) AS NumberOfOrders  

 FROM  

     Categories AS Cat  

 JOIN  

     Products AS P ON Cat.CategoryID = P.CategoryID  

 JOIN  

     [Order Details] AS OD ON P.ProductID = OD.ProductID  

 JOIN  

     Orders AS O ON OD.OrderID = O.OrderID  

 WHERE  

     YEAR(O.OrderDate) = @TargetYear -- Filter orders by year (BEFORE grouping)  

 GROUP BY  

     Cat.CategoryName,  

     P.ProductName  

 HAVING  

     SUM(OD.Quantity * OD.UnitPrice * (1 - OD.Discount)) >= @MinCategorySalesValue -- Filter groups by total sales  

 ORDER BY  

     ProductTotalSales DESC, -- Sort by product sales (highest first)  

     Cat.CategoryName ASC,   -- Tie-breaker 1  

     P.ProductName ASC       -- Tie-breaker 2 (ensures unique, consistent order)  

 OFFSET @OffsetRows ROWS  

 FETCH NEXT @PageSize ROWS ONLY;  

            
        

Create SQL query with SqlQueryBuilder 4

            
 var (sql4, parameters4) = new SqlQueryBuilder()  

     .Select()  

     .Columns("Cat.CategoryName", "P.ProductName")  

     .Column(new SUM(new ColumnArithmatic("OD.Quantity").MULTIPLY("OD.UnitPrice")  

         .MULTIPLY().StartBracket(1).SUBTRACT("OD.Discount").EndBracket()), "ProductTotalSales")  

     .Column(new COUNT(new Column("O.OrderID"), true), "NumberOfOrders")  

     .From("Categories", "Cat")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName("Products", "P")  

             .On(new Column("Cat.CategoryID").Equale(new Column("P.CategoryID"))),  

         new INNERJOIN().TableName("[Order Details]", "OD")  

             .On(new Column("P.ProductID").Equale(new Column("OD.ProductID"))),  

         new INNERJOIN().TableName("Orders", "O")  

             .On(new Column("OD.OrderID").Equale(new Column("O.OrderID")))  

     })  

     .Where(new Where(new YEAR(new Column("O.OrderDate")).Equale(1997)))  

     .GroupBy(new GroupBy("Cat.CategoryName", "P.ProductName"))  

     .Having(new Having(new SUM(  

             new ColumnArithmatic("OD.Quantity").MULTIPLY("OD.UnitPrice").MULTIPLY()  

                 .StartBracket(1).SUBTRACT("OD.Discount").EndBracket()).GreaterThanOrEqualeTo(50.00)))  

     .OrderBy(new OrderBy().SetColumnDescending("ProductTotalSales")  

         .SetColumnAscending("Cat.CategoryName").Offset(5,5))  

     .Build();  

            
        

Query build by SqlQueryBuilder 4

            
SELECT Cat.CategoryName,
       P.ProductName,
       SUM(OD.Quantity * OD.UnitPrice * (@pMAIN_2507200137022379290 - OD.Discount)) AS ProductTotalSales,
       COUNT(DISTINCT O.OrderID) AS NumberOfOrders
FROM Categories AS Cat
     INNER JOIN
     Products AS P
     ON Cat.CategoryID = P.CategoryID
     INNER JOIN
     [Order Details] AS OD
     ON P.ProductID = OD.ProductID
     INNER JOIN
     Orders AS O
     ON OD.OrderID = O.OrderID
WHERE YEAR(O.OrderDate) = @pMAIN_2507200137022379291
GROUP BY Cat.CategoryName, P.ProductName
HAVING SUM(OD.Quantity * OD.UnitPrice * (@pMAIN_2507200137022379292 - OD.Discount)) >= @pMAIN_2507200137022379293
ORDER BY ProductTotalSales DESC, Cat.CategoryName ASC
OFFSET @pMAIN_2507200137022379294 ROWS FETCH NEXT @pMAIN_2507200137022379295 ROWS ONLY;


            
        

Parameters (If used)

Query Results 4:

5. Search Functionality with Pagination

SQL Server Query 5

            
 -- Parameters for pagination and search  

 DECLARE @PageNumber INT = 1;  

 DECLARE @PageSize INT = 10;  

 DECLARE @SearchProductName NVARCHAR(40) = 'Chai'; -- Example: Search for 'Chai' or 'Cha' or 'syrup'  

   

 -- Calculate the number of rows to skip  

 DECLARE @OffsetRows INT = (@PageNumber - 1) * @PageSize;  

   

 SELECT  

     O.OrderID,  

     O.OrderDate,  

     C.CompanyName,  

     P.ProductName,  

     OD.Quantity,  

     OD.UnitPrice  

 FROM  

     Orders AS O  

 JOIN  

     [Order Details] AS OD ON O.OrderID = OD.OrderID  

 JOIN  

     Products AS P ON OD.ProductID = P.ProductID  

 JOIN  

     Customers AS C ON O.CustomerID = C.CustomerID  

 WHERE  

     P.ProductName LIKE '%' + @SearchProductName + '%' -- Search condition  

 ORDER BY  

     O.OrderDate DESC,   -- Sort by order date  

     O.OrderID DESC,     -- Tie-breaker for orders  

     P.ProductName ASC   -- Tie-breaker for products within an order  

 OFFSET @OffsetRows ROWS  

 FETCH NEXT @PageSize ROWS ONLY;  

            
        

Create SQL query with SqlQueryBuilder 5

            
 var (sql5, parameters5) = new SqlQueryBuilder()  

     .Select()  

     .Columns("O.OrderID","O.OrderDate","C.CompanyName","P.ProductName","OD.Quantity","OD.UnitPrice")  

     .From("Orders", "O")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName("[Order Details]", "OD")  

             .On(new Column("O.OrderID").Equale(new Column("OD.OrderID"))),  

         new INNERJOIN().TableName("Products", "P")  

             .On(new Column("OD.ProductID").Equale(new Column("P.ProductID"))),  

         new INNERJOIN().TableName("Customers", "C")  

             .On(new Column("O.CustomerID").Equale(new Column("C.CustomerID")))  

     })  

     .Where(new Where(new LIKE(new Column("P.ProductName"), "%chai%")))  

     .OrderBy(new OrderBy().SetColumnDescending("O.OrderDate")  

         .SetColumnDescending("O.OrderID")  

         .SetColumnAscending("P.ProductName").Offset(0, 10))  

     .Build();  

            
        

Query build by SqlQueryBuilder 5

            
SELECT O.OrderID,
       O.OrderDate,
       C.CompanyName,
       P.ProductName,
       OD.Quantity,
       OD.UnitPrice
FROM Orders AS O
     INNER JOIN
     [Order Details] AS OD
     ON O.OrderID = OD.OrderID
     INNER JOIN
     Products AS P
     ON OD.ProductID = P.ProductID
     INNER JOIN
     Customers AS C
     ON O.CustomerID = C.CustomerID
WHERE P.ProductName LIKE @pMAIN_2507200137022614900
ORDER BY O.OrderDate DESC, O.OrderID DESC, P.ProductName ASC
OFFSET @pMAIN_2507200137022614901 ROWS FETCH NEXT @pMAIN_2507200137022614902 ROWS ONLY;


            
        

Parameters (If used)

Query Results 5:

6. Pagination with Total Row Count in a Single Query

SQL Server Query 6

            
 -- Parameters for pagination  

 DECLARE @PageNumber INT = 2;  

 DECLARE @PageSize INT = 5;  

   

 -- Calculate the number of rows to skip  

 DECLARE @OffsetRows INT = (@PageNumber - 1) * @PageSize;  

   

 SELECT  

     OrderID,  

     CustomerID,  

     OrderDate,  

     Freight,  

     -- Window function to get the total count of rows without pagination  

     COUNT(*) OVER() AS TotalRowCount  

 FROM  

     Orders  

 WHERE  

     OrderDate >= '1997-01-01' -- Example filter  

 ORDER BY  

     OrderDate ASC,  

     OrderID ASC  

 OFFSET @OffsetRows ROWS  

 FETCH NEXT @PageSize ROWS ONLY;  

            
        

Create SQL query with SqlQueryBuilder 6

            
 var (sql6, parameters6) = new SqlQueryBuilder()  

     .Select()  

     .Columns("OrderID","CustomerID","OrderDate","Freight")  

     .Column(new COUNT(new Column("OrderID")).OVER(new OVER()), "TotalRowCount")  

     .From("Orders", "O")  

     .Where(new Where(new Column("OrderDate").GreaterThanOrEqualeTo("1997-01-01")))  

     .OrderBy(new OrderBy().SetColumnAscending("OrderDate")  

         .SetColumnAscending("OrderID").Offset(5, 5))  

     .Build();  

            
        

Query build by SqlQueryBuilder 6

            
SELECT OrderID,
       CustomerID,
       OrderDate,
       Freight,
       COUNT(OrderID) OVER () AS TotalRowCount
FROM Orders AS O
WHERE OrderDate >= @pMAIN_2507200137022711860
ORDER BY OrderDate ASC, OrderID ASC
OFFSET @pMAIN_2507200137022711861 ROWS FETCH NEXT @pMAIN_2507200137022711862 ROWS ONLY;


            
        

Parameters (If used)

Query Results 6:

7. For each order, find the details of the previous order placed by the same customer, sorted by OrderDate

SQL Server Query 7

            
 SELECT  

     O.OrderID,  

     O.CustomerID,  

     O.OrderDate,  

     LAG(O.OrderID) OVER (PARTITION BY O.CustomerID ORDER BY O.OrderDate) AS PreviousOrderID,  

     LAG(O.OrderDate) OVER (PARTITION BY O.CustomerID ORDER BY O.OrderDate) AS PreviousOrderDate,  

     LAG(SUM(OD.Quantity * OD.UnitPrice)) OVER (PARTITION BY O.CustomerID ORDER BY O.OrderDate) AS PreviousOrderTotal  

 FROM  

     Orders AS O  

 JOIN  

     [Order Details] AS OD ON O.OrderID = OD.OrderID  

 GROUP BY  

     O.OrderID, O.CustomerID, O.OrderDate -- Group by order details before window function  

 ORDER BY  

     O.CustomerID, O.OrderDate  

 OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY  

            
        

Create SQL query with SqlQueryBuilder 7

            
 var (sql7, parameters7) = new SqlQueryBuilder()  

     .Select()  

     .Columns("O.OrderID","O.CustomerID","O.OrderDate")  

     .Column(new LAG(new Column("O.OrderID")).PARTITION_BY(new Column("O.CustomerID"))  

         .ORDER_BY(new OrderBy().SetColumnAscending("O.OrderDate")), "PreviousOrderID")  

     .Column(new LAG(new Column("O.OrderDate")).PARTITION_BY(new Column("O.CustomerID"))  

         .ORDER_BY(new OrderBy().SetColumnAscending("O.OrderDate")), "PreviousOrderDate")  

     .Column(new LAG(new SUM(new ColumnArithmatic("OD.Quantity").MULTIPLY("OD.UnitPrice")))  

         .PARTITION_BY(new Column("O.CustomerID"))  

         .ORDER_BY(new OrderBy().SetColumnAscending("O.OrderDate")), "PreviousOrderTotal")  

     .From("Orders", "O")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName("[Order Details]", "OD")  

             .On(new Column("O.OrderID").Equale(new Column("OD.OrderID")))  

     })  

     .GroupBy(new GroupBy("O.OrderID", "O.CustomerID", "O.OrderDate"))  

     .OrderBy(new OrderBy().SetColumnAscending("O.CustomerID")  

         .SetColumnAscending("O.OrderDate").Offset(10, 10))  

     .Build();  

            
        

Query build by SqlQueryBuilder 7

            
SELECT O.OrderID,
       O.CustomerID,
       O.OrderDate,
       LAG(O.OrderID, @pMAIN_2507200137022785100) OVER (PARTITION BY O.CustomerID ORDER BY O.OrderDate ASC) AS PreviousOrderID,
       LAG(O.OrderDate, @pMAIN_2507200137022785101) OVER (PARTITION BY O.CustomerID ORDER BY O.OrderDate ASC) AS PreviousOrderDate,
       LAG(SUM(OD.Quantity * OD.UnitPrice), @pMAIN_2507200137022785102) OVER (PARTITION BY O.CustomerID ORDER BY O.OrderDate ASC) AS PreviousOrderTotal
FROM Orders AS O
     INNER JOIN
     [Order Details] AS OD
     ON O.OrderID = OD.OrderID
GROUP BY O.OrderID, O.CustomerID, O.OrderDate
ORDER BY O.CustomerID ASC, O.OrderDate ASC
OFFSET @pMAIN_2507200137022785103 ROWS FETCH NEXT @pMAIN_2507200137022785104 ROWS ONLY;


            
        

Parameters (If used)

Query Results 7: