writing union statement with 2 order by's

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)

writing union statement with 2 order by's

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-06-20 : 03:17:22

Hi,

I'm trying to write a UNION statement with 2 selects and can't figure out for the life of me how to do it.

select top 10 * from tblusers ORDER BY dateJoined
UNION
select top 10 * from tblusers ORDER BY lastLogin

The union works fine without the order by's, but I really need the order by's in there and I don't understand why its so difficult to do. Surely there must be some other strategy?

Any help is much appreciated.. thanks!!
mike123

How can I reproduce this results ?

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-06-20 : 03:28:45

What about using this:

Select * from
(
select top 10 * from tblusers
UNION
select top 10 * from tblusers
) as drv order by dateJoined,lastLogin

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-06-20 : 03:35:25

quote:
Originally posted by sunil

What about using this:

Select * from
(
select top 10 * from tblusers
UNION
select top 10 * from tblusers
) as drv order by dateJoined,lastLogin

I don't believe this is going to bring back my intended results as the 2 UNIONed queries are going to be the exact same results ?

Thanks!
mike123

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-06-20 : 03:39:11

put them into derived tables....


declare @table table (col1 int)
insert into @table 
select 1 union
select 2 union
select 3

select * from (select top 2 * from @table order by col1) a
union all
select * from (select top 2 * from @table order by col1 desc) b

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-06-20 : 03:42:29

quote:

Originally posted by elancaster

put them into derived tables....


declare @table table (col1 int)
insert into @table 
select 1 union
select 2 union
select 3

select * from (select top 2 * from @table order by col1) a
union all
select * from (select top 2 * from @table order by col1 desc) b

Hi Em,

Great. Is this the standard / only way ? or just the best way to approach this situation ?

I have to write a bunch of similar queries, so trying to find the best option.

Thanks again!!
mike123

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-06-20 : 03:46:23

it's the only way i can think of

dass05555
Yak Posting Veteran

55 Posts

Posted - 2008-06-20 : 03:47:24

Why can't u try this,

select top 10 * from tblusers
UNION
select top 10 * from tblusers ORDER BY lastLogin,dateJoined

cool...,

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-06-20 : 03:53:55

quote:
Originally posted by dass05555

Why can't u try this,

select top 10 * from tblusers
UNION
select top 10 * from tblusers ORDER BY lastLogin,dateJoined

cool...,

this is select results I don't want as well. both tables would return incorrect data

thx :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources