CODEWITHSUNDEEP
sql-server
BumbleBee
BumbleBee

Reputation: 10789

Performing 'UNION' on the queries with 'ORDER BY'

I am getting a syntax error at the Union all. I know I can't do this. But Can somebody help me out.

SELECT
ID, 
date1, 
date2 row_number() OVER (PARTITION BY ID ORDER BY date1 DESC )  as RN1, 
1 as Range
FROM 
(
SELECT ID,date1,
rn = row_number() OVER (PARTITION BY ID ORDER BY date1 )
FROM listing_History   (nolock)
WHERE [date1] <=   CONVERT(DATE,DATEADD(MONTH, -6, GETDATE()))  AND 
ID in (SELECT txt FROM [dbo].[fn_ListToTable](@listStr, ',')) 
)  AS A ORDER BY date1 DESC, date2 DESC

UNION ALL 

SELECT
ID, 
Date1, 
date2 row_number() OVER (PARTITION BY ID ORDER BY date1 DESC )  as RN1, 
1 as Range
FROM 
(
SELECT 
ID,
date1,
rn = row_number() OVER (PARTITION BY ID ORDER BY date1 )
FROM listing_History   (nolock)
WHERE [status_date] <=   CONVERT(DATE,DATEADD(MONTH, -3, GETDATE()) -1)
AND 
ID in (SELECT txt FROM [dbo].[fn_ListToTable](@listStr, ',')) 
) AS A ORDER BY date1 DESC,date2 desc

Upvotes: 2

Views: 61

Answers (2)

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

There should always one Order by on the very last select of UNION and UNION ALL

SELECT  
ID,
date1,
date2 row_number() OVER (PARTITION BY ID ORDER BY date1 DESC )  as RN1,
1 as Range
FROM 
(
SELECT ID,date1,
rn = row_number() OVER (PARTITION BY ID ORDER BY date1 )
FROM listing_History (nolock)
WHERE [date1] <= CONVERT(DATE,DATEADD(MONTH, -6, GETDATE())) 
AND 
ID in (SELECT txt FROM [dbo].[fn_ListToTable](@listStr, ',')) 
) AS A --ORDER BY date1 DESC, date2 DESC Remove order by from here

UNION ALL 

SELECT
ID, 
Date1, 
date2 row_number() OVER (PARTITION BY ID ORDER BY date1 DESC )  as RN1, 
1 as Range
FROM 
(
SELECT ID,date1,
rn = row_number() OVER (PARTITION BY ID ORDER BY date1 )
FROM listing_History with(nolock)
WHERE [status_date] <=   CONVERT(DATE,DATEADD(MONTH, -6, GETDATE()))
AND 
ID in (SELECT txt FROM [dbo].[fn_ListToTable](@listStr, ',')) 
)AS A
ORDER BY date1 DESC, date2 DESC

Upvotes: 1

coge.soft
coge.soft

Reputation: 1674

Remove all ORDER BYs from every UNION except the last one and make sure the column names you reference in the ORDER BY correspond to the first SELECT.

Upvotes: 1

Related Questions