TransactSQL union all AND paging

Question

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 ?

Try the solution here: http://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server — The Shooter, Aug 16 '16 at 08:18
To be fair, this problem should be split cleanly to what you want to do. You want the first? A set of 20 orders with their respective details included. Therefore, you describe 2 sets of queries, where {A} = 20 {Orders} and {B} = N{Ordesimples}. If B can have more then one row, use the intersection of the joins to return all data in those 20 orders. If not, well you can use proper subqueries to limit the result. Either way, this problem should be simple. ~my 2 cents — clifton_h, Aug 16 '16 at 08:42
@clifton_h Well, I've got 4 tables : OrderDetails, Orders, OrderLines, Supplements : An order can have multiple orderdetails but orderlines can have only one order (orderlines can have multiple supplements). I've updated the query to make it more readable — Musab Gosset, Aug 16 '16 at 09:07

Tim Biegeleisen · Answer 1 · 2016-08-16T08:26:02.913

2

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.

edited Aug 16 '16 at 08:26

answered Aug 16 '16 at 08:09

Tim Biegeleisen

502,043
27
286
360

Sub sequencing the query will not work as it won't return all the orderlines or supplements of the order, while paging – Musab Gosset Aug 16 '16 at 08:14
I don't see why this would be the case. Can you explain this? – Tim Biegeleisen Aug 16 '16 at 08:15
I want to return only 20 by 20 orders but ALL their orderlines and supplements – Musab Gosset Aug 16 '16 at 08:23
1

Your query is _way_ too long for any chance of someone writing a complete solution IMO. You might want to pare it down a bit. – Tim Biegeleisen Aug 16 '16 at 08:27
@Musab.Gosset then by all means separate it from the query (or add a CTE? ) so that you use a join to return these results. You're almost there. – clifton_h Aug 16 '16 at 08:30

TransactSQL union all AND paging

1 Answers1