Order by in Union all
-
Hi,
Is there any method by which i can use ORDER BY clause in UNION ALL.
Thnx,
Vivek
/**A strong positive mental attitude will create more miracles than any wonder drug**/
-
Sure, try something like
select 1 as seq
union all
select 0
order by seq
If this doesn't help, please post more details.
-
USE pubs
--SELECT Count(*) FROM authors WHERE ZIP <= 93000
--SELECT Count(*) FROM authors WHERE ZIP > 93000 AND ZIP < 94500
--SELECT Count(*) FROM authors WHERE ZIP >= 94500
SELECT au_id,au_fname,zip FROM authors WHERE ZIP <= 93000 --ORDER BY zip DESC
UNION ALL
SELECT au_id,au_fname,zip FROM authors WHERE ZIP >= 94500 --ORDER BY zip DESC
UNION ALL
SELECT au_id,au_fname,zip FROM authors WHERE ZIP > 93000 AND ZIP < 94500 --ORDER BY zip DESC
ORDER BY zip DESC
/*
Is ORDER BY can be written as above (which is commented out) not at the bottom. Is there any method to do so.
Thnx,
Vivek
*/
/**A strong positive mental attitude will create more miracles than any wonder drug**/
-
If you want to use Order By for every select clause of union clause then you need to use the TOP clause like the following
Select * from (
Select TOP 1000 au_id,au_fname,zip FROM authors WHERE ZIP <= 93000 ORDER BY Zip
Union
Select TOP 1000 au_id,au_fname,zip FROM authors WHERE ZIP >= 94500 ORDER BY Zip
Union
Select TOP 1000 SELECT au_id,au_fname,zip FROM authors WHERE ZIP > 93000 AND ZIP < 94500 ORDER BY Zip
) as temptable
Else you can follow the tip by Jesper Mygind
-
Do you want authors with zips between 93000 and 94500 to appear last in your select? Then try something like
SELECT au_id,au_fname,zip FROM authors
order by
case when zip <= 93000 then 0 when zip >= 94500 then 1 else 2 end,
zip desc
-
and if the Tables were to be different you can add a "helper" column
like:
Select Col1, Col2, 1 as helper from Table1 where xyz...
union all
Select Col1, Col2, 2 from Table2 where xyz...
union all
Select Col1, Col2, 3 from Table3 where xyz...
Order by Helper
* Noel -
Though doing a UNION and not a UNION ALL probably will resort your data. Possibily in another order.
And if you want all the rows and not a fixed number there is always the option to use the PERCENT.
Example: SELECT TOP 100 PERCENT * FROM authors
//Hanslindgren
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply