Stupid Union Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stupid Union Question

Author

Topic

ConradK
Posting Yak Master

140 Posts

Posted - 2010-05-14 : 12:18:22

*sigh*, not sure why I can't find an answer to this.

t1
1

t2
2

t3
3

select * from t1
union
select * from t2
union
select * from t3

I'm expecting something like

1
2
3

but am getting

3
1
2

.... why?!?!? Of course I've simplified the data a bit here. The data requires a header and footer, I'm using union to create thsoe headers and footers, but the footer is floating to the top.

Put simply, how do I controll the order of the queries it unions?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-14 : 12:20:52

Use it like ...

select * from 
(
select * from t1
union
select * from t2
union 
select * from t3
) t
order by <desiredfield>

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-14 : 12:39:10

quote:
Originally posted by ConradK
[br<snip>
.... why?!?!? Of course I've simplified the data a bit here. The data requires a header and footer, I'm using union to create thsoe headers and footers, but the footer is floating to the top.

Put simply, how do I controll the order of the queries it unions?

The WHY is that there is NO order without an ORDER BY clause. This the sample provided by Vijayisonly includes that and thus an ordered result set. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-14 : 12:50:04

there's no concept of first and last in SQL table without use of ORDER BY

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2010-05-14 : 12:58:07

Ah, but the OP said

quote:

Put simply, how do I controll the order of the queries it unions?

select * from
(
select *, 1 AS Query from t1
union
select *, 2 AS Query from t2
union
select *, 3 AS Query from t3
) t
ORDER BY Query

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

ConradK
Posting Yak Master

140 Posts

Posted - 2010-05-14 : 14:06:31

X002548! Brilliant!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources