UNION - 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

SQL Server 2005 Forums

Transact-SQL (2005)

UNION

Author

Topic

RSQLSERVER
Starting Member

5 Posts

Posted - 2009-08-30 : 19:24:22

There is a table A

i
1
4

THere is a table B

i
2
3

Now,

select * from a
union
select * from b

Returns

i
1
2
3
4

instead of

i
1
4
2
3

? ?????? ??? ?????????? ??? ???? ???????? ????

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-08-30 : 20:05:17

That's because the order of data is meaningless without an ORDER BY. To achieve what you want, try this:

select column1 from (
select column1, 1 AS whichTable from a
union
select column1, 2 from b) t
order by whichTable asc

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

RSQLSERVER
Starting Member

5 Posts

Posted - 2009-08-31 : 00:04:19

THanks a lot Tara, but my question is why

i
1
2
3
4

is the output as against the expected output:

i
1
4
2
3

? ?????? ??? ?????????? ??? ???? ???????? ????

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-31 : 02:23:18

quote:
Originally posted by RSQLSERVER

THanks a lot Tara, but my question is why

i
1
2
3
4

is the output as against the expected output:

i
1
4
2
3

? ?????? ??? ?????????? ??? ???? ???????? ????

It is because UNION when removing the duplicates also sort the result set (But the Order is not always guaranteed. Due to behavioural changes it may work in one version and not in other version)

If you want the exact order, use UNION ALL

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources