CODEWITHSUNDEEP
sqlsql-serverperformancet-sqlsql-server-2012
Musab Gosset
Musab Gosset

Reputation: 49

TransactSQL union all AND paging

I need to return, as per example, 20 orders but I dunno how to paginate correctly in my query.

The following query will return 20 rows (order, orderlines, supplements) but not only the 20 first orders.

SELECT *
FROM 
(
       SELECT *
       FROM ( SELECT *
             FROM  [WS].[viewOrderDetail] AS [Extent1]  
             WHERE [Extent1].CustomerID IN (2,7,8,9)        
       )  AS [Project1]) AS [Cmd] 
LEFT JOIN [WS].[viewOrders] AS [orders] ON ([orders].Id = [cmd].OrderId)
LEFT JOIN [WS].[viewOrderLines] AS [OrderLines] ON ([OrderLines].OrderId= [Cmd].[OrderId])
LEFT JOIN [WS].[viewOrderLineSupplements] AS [Supp]  ON (Supp.OrderLineId = OrderLines.Id)
WHERE cmd.OrderId LIKE '%11301%'  

union all 

SELECT *
FROM 
(
       SELECT *
       FROM ( SELECT *
             FROM  [WS].[viewOrderDetail] AS [Extent1]  
             WHERE [Extent1].CustomerID IN (2,7,8,9)         
       )  AS [Project1]) AS [Cmd] 
LEFT JOIN [WS].[viewOrders] AS [orders] ON ([orders].Id = [cmd].OrderId)
LEFT JOIN [WS].[viewOrderLines] AS [OrderLines] ON ([OrderLines].OrderId= [Cmd].[OrderId])
LEFT JOIN [WS].[viewOrderLineSupplements] AS [Supp]  ON (Supp.OrderLineId = OrderLines.Id)
WHERE OrderLines.Id LIKE '%11301%'  
ORDER BY [Cmd].Id ASC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

Actual result : Actual result

Should result : Should result

Do you have any idea of how I can paginate properly ?

Upvotes: 0

Views: 336

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521419

I won't try to reproduce your entire query here, but one option would be to subquery what you have and select out the first 20 rows, ordering ascending by the order date:

SELECT t.*
FROM
(
    /* your UNION query here */
) t
ORDER BY t.InputDate OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

A modification of this query would be to first obtain the first 20 orders by date, then joining the resultant table to bring in the corresponding orderlines and supplements.

Upvotes: 2

Related Questions