CODEWITHSUNDEEP
sql-server
SF Developer
SF Developer

Reputation: 5384

Union All combine 2 tables each one using a different sorting and paging

I'm using MS-SQL 2012 and have 2 tables that contains similar data field types.

I'm writing a stored procedure that needs to get 10 records from Table1 using a specific sort order and 10 records from Table2 using a different sort order.

I also need to use paging

Something like

SELECT Field1, Field2, Field3   
  FROM TableAAAA  
ORDER BY FieldSort1 DESC    --> DateTime
OFFSET (@PageIndex-1) * 10 ROWS   
  FETCH NEXT @PageSize ROWS ONLY  

UNION ALL

SELECT Field1, Field2, Field3   
  FROM TableBBBB   
ORDER BY FieldSort2    --> INT
OFFSET (@PageIndex-1) * 10 ROWS   
  FETCH NEXT @PageSize ROWS ONLY

If I use a UNION ALL, I can only sort at the end of it so I cannot use the code above.

Upvotes: 1

Views: 250

Answers (2)

Hogan
Hogan

Reputation: 70523

Here is a way (since you know the first one is only going to be page size...

WITH t1 AS
(

  SELECT Field1, Field2, Field3, ROW_NUMBER() OVER (ORDER BY FieldSort1) AS outerSort1, 0 as outerSort2
  FROM TableAAAA  
  ORDER BY FieldSort1
  OFFSET (@PageIndex-1) * 10 ROWS   
    FETCH NEXT @PageSize ROWS ONLY  
), t2 AS
(
  SELECT Field1, Field2, Field3, 0 as outerSort1, ROW_NUMBER() OVER (ORDER BY FieldSort2) AS outerSort2   
  FROM TableBBBB   
  ORDER BY FieldSort2
  OFFSET (@PageIndex-1) * 10 ROWS   
    FETCH NEXT @PageSize ROWS ONLY  
)
SELECT Field1, Field2, Field3 FROM T1
UNION ALL
SELECT Field1, Field2, Field3 FROM T2
ORDER BY outerSort1, outerSort2

Upvotes: 3

Tab Alleman
Tab Alleman

Reputation: 31785

You could use either derived tables or CTEs:

WITH cteA AS (
SELECT Field1, Field2, Field3
  FROM TableAAAA  
ORDER BY FieldSort1
OFFSET (@PageIndex-1) * 10 ROWS   
  FETCH NEXT @PageSize ROWS ONLY  
),
cteB AS (
SELECT Field1, Field2, Field3
  FROM TableBBBB   
ORDER BY FieldSort2
OFFSET (@PageIndex-1) * 10 ROWS   
  FETCH NEXT @PageSize ROWS ONLY  
)
SELECT * FROM cteA
UNION ALL
SELECT * FROM cteB

Upvotes: 2

Related Questions