CODEWITHSUNDEEP
sqlsql-serverunion
I Love Stackoverflow
I Love Stackoverflow

Reputation: 6868

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.

Upvotes: 1

Views: 782

Answers (2)

Dariusz Bielak
Dariusz Bielak

Reputation: 415

Remove the order by from the first part of the Union

Upvotes: 2

Felix Pamittan
Felix Pamittan

Reputation: 31879

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.

Upvotes: 3

Related Questions