Learning Learning - 5 months ago 8
SQL Question

Getting incorrect syntax on union on combining two results from single table

I want to combine 2 results from a single table but getting incorrect syntax near

union all
.

SELECT
ID as 'ABC','1' as 'PQR','2' as 'XYZ', Total as 'LMN'
FROM
Employee
ORDER BY
ID

UNION ALL

SELECT
ID as 'ABC','1' as 'PQR','2' as 'XYZ', Total as 'LMN'
FROM
Employee
ORDER BY
ID


But above query fails with the error


Incorrect syntax near union


Note: I have used union all because I don't want to remove duplicate rows.

Can anybody help me with this?

Answer

You need to remove the first ORDER BY clause:

SELECT
    ID AS 'ABC',
    '1' AS 'PQR',
    '2' AS 'XYZ',
    Total AS 'LMN'
FROM Employee
UNION ALL
SELECT
    ID AS 'ABC',
    '1' AS 'PQR',
    '2' AS 'XYZ',
    Total AS 'LMN'
FROM Employee
ORDER BY ID

Example F of the UNION documentation says:

When any SELECT statement in a UNION statement includes an ORDER BY clause, that clause should be placed after all SELECT statements.

Comments