How to ORDER BY one query only in a UNION ALL without subquery

Question

I'm creating a list from unrelated data using UNION ALL in order to be saved to Excel in SQL Server 2008. Only one of the SELECT statements will have an ORDER BY and that is where my issue arises with the following error message

'The multi-part identifier "p.Category" could not be bound.'

This is a simplified working code example that I prefer to the second one below but breaks when uncommenting the first SELECT query:

CREATE TABLE #People(Category varchar(50), [Name] varchar(50));

INSERT INTO #People VALUES(1, 'Steve');
INSERT INTO #People VALUES(99, 'Anna');
INSERT INTO #People VALUES(2, 'Sarah'); 

-- Unrelated data SELECT query
--SELECT 'Best Employee' AS Matrix
--     , 'Alan' AS [Name]
--UNION ALL

SELECT CASE
           WHEN p.Category = 1
           THEN 'Very Active'
           WHEN p.Category = 2
           THEN 'Active '
           ELSE 'Departed'
       END AS Matrix
     , p.name AS [Name]
FROM #People p
ORDER BY p.Category;

DROP TABLE #People;

After some research I found that I could rewrite the query in the following way, which would give me the desired result in this small example but not something that I would consider ideal in my real case. Is there a way to give me the correct result without resorting to sub queries like in the working example below?

CREATE TABLE #People(Category varchar(50), [Name]  varchar(50));

INSERT INTO #People VALUES(1, 'Steve');
INSERT INTO #People VALUES(99, 'Anna');
INSERT INTO #People VALUES(2, 'Sarah'); 

SELECT *
FROM
(
    -- Unrelated data SELECT query
    SELECT 'Best Employee' AS Matrix
         , 'Alan' AS [Name]
         , '' AS Category

    UNION ALL

    SELECT CASE
               WHEN p.Category = 1
               THEN 'Very Active'
               WHEN p.Category = 2
               THEN 'Active '
               ELSE 'Departed'
           END Matrix
         , p.name AS [Name]
         , p.Category Category
    FROM #People p
) x
ORDER BY x.Category;

DROP TABLE #People;

score 1 · Accepted Answer · answered Mar 30 '17 at 04:26

The ORDER BY doesn't apply to just one SELECT, it applies to the whole result set, to the whole result after all rows are UNIONed. So, you need to include columns that will be used for sorting in each SELECT of the UNION ALL.

For example, the following is wrong syntax, because ORDER BY has to be after the last SELECT of the UNION.

SELECT CASE
           WHEN p.Category = 1
           THEN 'Very Active'
           WHEN p.Category = 2
           THEN 'Active '
           ELSE 'Departed'
       END AS Matrix
     , p.name AS [Name]
FROM #People p
ORDER BY p.Category

UNION ALL

SELECT 'Best Employee' AS Matrix
     , 'Alan' AS [Name]

Your second example from the question can be written in a simpler form, without sub-query:

SELECT CASE
           WHEN p.Category = 1
           THEN 'Very Active'
           WHEN p.Category = 2
           THEN 'Active '
           ELSE 'Departed'
       END AS Matrix
     , p.name AS [Name]
     , p.Category
FROM #People p

UNION ALL

SELECT 'Best Employee' AS Matrix
     , 'Alan' AS [Name]
     , NULL AS Category  --- or '' AS Category

ORDER BY Category;

In any case, the column Category has to be added in each SELECT statement of the UNION. I don't see how you can avoid it.

score 0 · Answer 2 · answered Mar 30 '17 at 01:50

If your issue is that your ordering is causing the 2 tables from being mixed up, you could add a column so that the 1st select result set would be displayed first always.

CREATE TABLE #People(Category varchar(50), [Name]  varchar(50));

INSERT INTO #People VALUES(1, 'Steve');
INSERT INTO #People VALUES(99, 'Anna');
INSERT INTO #People VALUES(2, 'Sarah'); 

SELECT Matrix, Name, Category
FROM
(
    -- Unrelated data SELECT query
    SELECT 'Best Employee' AS Matrix
         , 'Alan' AS [Name]
         , '' AS Category
         , 1 as Ord

UNION ALL

    SELECT CASE
               WHEN p.Category = 1
               THEN 'Very Active'
               WHEN p.Category = 2
               THEN 'Active '
               ELSE 'Departed'
           END Matrix
         , p.name AS [Name]
         , p.Category Category,
         2 as Ord
    FROM #People p
) x
ORDER BY x.Ord --,Additional Order by columns

It's not the ordering that I'm worried about just the sheer number of `SELECT` statements really. My hope was to only having to change the statement with the `ORDER BY` and leave all others as they are. — sams, Mar 30 '17 at 02:40

How to ORDER BY one query only in a UNION ALL without subquery

2 Answers2