How can I combine Separate SQL queries, Each With ORDER BYs Into One Query?

Question

I am wanting, in theory, to basically create two separate tables with the same columns, order them each separately as desired, and then simply slap one below the other and retain that order.

I have tried doing this using methods suggested elsewhere (see below) such as:

SELECT * FROM
(SELECT [company name], [appointment call back 1], [appointment call back 2], [appointment date 1], [appointment date 2] FROM Vantrack_Tulsa WHERE [appointment call back 1] BETWEEN '6/1/2016' AND '6/1/2017' 
                          OR [appointment call back 2] BETWEEN '6/1/2016' AND '6/1/2017' 
                          ORDER BY [company name] ASC) t
                          UNION ALL
SELECT * FROM
(SELECT [company name], [appointment call back 1], [appointment call back 2], [appointment date 1], [appointment date 2] FROM Vantrack_Tulsa WHERE [appointment date 1] BETWEEN '6/1/2016' AND '6/1/2017'
                          OR [appointment date 2] BETWEEN '6/1/2016' AND '6/1/2017'
                          ORDER BY [company name] ASC) s

But I get:

Msg 1033, Level 15, State 1, Line 8
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
Msg 1033, Level 15, State 1, Line 13
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

I've also tried it this way:

WITH x as
(SELECT [company name], [appointment call back 1], [appointment call back 2], [appointment date 1], [appointment date 2] FROM Vantrack_Tulsa WHERE [appointment call back 1] BETWEEN '6/1/2016' AND '6/1/2017' 
                              OR [appointment call back 2] BETWEEN '6/1/2016' AND '6/1/2017' 
                              ORDER BY [company name] ASC),
y as
(SELECT [company name], [appointment call back 1], [appointment call back 2], [appointment date 1], [appointment date 2] FROM Vantrack_Tulsa WHERE [appointment date 1] BETWEEN '6/1/2016' AND '6/1/2017'
                              OR [appointment date 2] BETWEEN '6/1/2016' AND '6/1/2017'
                              ORDER BY [company name] ASC)
SELECT * FROM x UNION ALL SELECT * FROM y

But I get:

Msg 1033, Level 15, State 1, Line 8
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'ORDER'.

Probably for much the same reason.

I saw that this question has already been asked but the supposed solution either never really worked or doesn't work any more.

Some SO Answers I Checked

• How to combine two SQL queries with different ORDER BY clauses
• Using different order by with union
• SQL Server query with union and different order by to each section?

Is there something I'm overlooking? Any way to do this?

Answer 1

You could also use ROW_NUMBER() if you like. Something like this:

SELECT
  *
FROM
(
  SELECT
    [company name],
    [appointment call back 1],
    [appointment call back 2],
    [appointment date 1],
    [appointment date 2],
    'A' + CAST(ROW_NUMBER() OVER(ORDER BY [company name]) as varchar(50)) as Sequence
  FROM
    Vantrack_Tulsa
  WHERE
    [appointment call back 1] BETWEEN '6/1/2016' AND '6/1/2017' OR
    [appointment call back 2] BETWEEN '6/1/2016' AND '6/1/2017' 

UNION ALL

SELECT
  [company name],
  [appointment call back 1],
  [appointment call back 2],
  [appointment date 1],
  [appointment date 2],
  'B' + CAST(ROW_NUMBER() OVER(ORDER BY [company name]) as varchar(50)) as Sequence
FROM
  Vantrack_Tulsa
WHERE
  [appointment date 1] BETWEEN '6/1/2016' AND '6/1/2017' OR
  [appointment date 2] BETWEEN '6/1/2016' AND '6/1/2017'
) x

ORDER BY
  x.Sequence

Answer 2

You need to OrderBy the final result of UNION ALL, so place the order by clause in the end.

SELECT * FROM(
(SELECT [company name], [appointment call back 1], [appointment call back 2], [appointment date 1], [appointment date 2] FROM Vantrack_Tulsa WHERE [appointment call back 1] BETWEEN '6/1/2016' AND '6/1/2017' 
                          OR [appointment call back 2] BETWEEN '6/1/2016' AND '6/1/2017') t
UNION ALL

(SELECT [company name], [appointment call back 1], [appointment call back 2], [appointment date 1], [appointment date 2] FROM Vantrack_Tulsa WHERE [appointment date 1] BETWEEN '6/1/2016' AND '6/1/2017'
                          OR [appointment date 2] BETWEEN '6/1/2016' AND '6/1/2017') s
) Q
Order by Q.[company_name] ASC

Answer 3

A quick outline of how you could do this:

;WITH CTE_Sets AS (
    SELECT 1 AS set_order, <other columns here>
    FROM Some_Table
    UNION ALL
    SELECT 2 AS set_order, <other columns here>
    FROM Some_Table
)
SELECT <columns>
FROM CTE_Sets
ORDER BY
    set_order,
    CASE set_order
        WHEN 1 THEN <order criteria for set #1>
        WHEN 2 THEN <order criteria for set #2>
    END

Answer 4

A way to do this is by adding another column to the SELECT for the X and Y queries to denote what order you want them to appear in, and ordering by that value.

This should do what you want:

With x
As (Select [company name],
           [appointment call back 1],
           [appointment call back 2],
           [appointment date 1],
           [appointment date 2],
           1 As Ord
    From   Vantrack_Tulsa
    Where  [appointment call back 1]
           Between '6/1/2016' And '6/1/2017'
           Or [appointment call back 2]
           Between '6/1/2016' And '6/1/2017'
   ),
     y
As (Select [company name],
           [appointment call back 1],
           [appointment call back 2],
           [appointment date 1],
           [appointment date 2],
           2 As Ord
    From   Vantrack_Tulsa
    Where  [appointment date 1]
           Between '6/1/2016' And '6/1/2017'
           Or [appointment date 2]
           Between '6/1/2016' And '6/1/2017'
   )
Select [company name],
       [appointment call back 1],
       [appointment call back 2],
       [appointment date 1],
       [appointment date 2]
From   x
Union All
Select [company name],
       [appointment call back 1],
       [appointment call back 2],
       [appointment date 1],
       [appointment date 2]
From   y
Order By Ord, [company name];