SQLServerCentral / SQL Server 7,2000 / T-SQL / order by inside union (selects) / Latest Posts

RE: order by inside union (selects)

joelucas — Sun, 12 Oct 2003 22:42:00 GMT

Try out this.Select '2' 'orderfield', field1, Field2 from table1unionSelect '1' 'orderfield', field3, Field4 from table2order by orderfield, <field> you want to order byYou 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.

RE: order by inside union (selects)

Jonathan — Fri, 10 Oct 2003 17:59:00 GMT

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,cFROM (SELECT a, b, c, 1 sFROM dWHERE x = 1UNIONSELECT a, b, c, 2FROM dWHERE x = 2) aORDER BY c, s, b

--Jonathan

RE: order by inside union (selects)

gljjr — Fri, 10 Oct 2003 17:08:00 GMT

You might be able to get around this if you use TOP 100 PERCENT on your subquery that you want to order by.

 use pubsgoSELECT title, 'titles' WHEREFROM FROM titlesUNIONSELECT title, 'author' WHEREFROM FROM (SELECT TOP 5 au_fname title FROM authors ORDER BY au_fname DESC) AORDER BY title

Gary JohnsonMicrosoft Natural Language GroupDBA, Sr. DB Engineer

order by inside union (selects)

ddev — Fri, 10 Oct 2003 16:48:00 GMT

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,cfrom dwhere x = 1union(select a,b,cfrom dwhere x = 2order by b)order by cSo 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.