October 10, 2003 at 4:48 pm
I have a series of select statements that are unioned togther. 1 of them I would like to be able to sort in addition to the sorting of the whole final table. I get a syntax error that prevent me from doing this. Can anyone provide some insight or mods to the example code? thx
--------------
select a,b,c
from d
where x = 1
union
(select a,b,c
from d
where x = 2
order by b)
order by c
So in this example, the second select statement would be ordered by b, then the whole results would be ordered by c.
But alas - syntax error prevents this.
thx for any comments.
October 10, 2003 at 5:08 pm
You might be able to get around this if you use TOP 100 PERCENT on your subquery that you want to order by.
use pubs
go
SELECT title, 'titles' WHEREFROM FROM titles
UNION
SELECT title, 'author' WHEREFROM FROM (SELECT TOP 5 au_fname title FROM authors ORDER BY au_fname DESC) A
ORDER BY title
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
October 10, 2003 at 5:59 pm
The result set from a UNION is not guaranteed to be in any order, so, even if you could order the selects that are unioned, the order of the results would not be meaningful. That's why your statement produces an error. Perhaps something like this will work:
SELECT a,b,c
FROM (
SELECT a, b, c, 1 s
FROM d
WHERE x = 1
UNION
SELECT a, b, c, 2
FROM d
WHERE x = 2) a
ORDER BY c, s, b
--Jonathan
--Jonathan
October 12, 2003 at 10:42 pm
Try out this.
Select '2' 'orderfield', field1, Field2 from table1
union
Select '1' 'orderfield', field3, Field4 from table2
order by orderfield, <field> you want to order by
You could think of re-writing your query on these lines by introducing dummy order by fields that would contain constants. It is not possible to order sub result sets in a Union.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply