Individual sorting on select statements w/unionall

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 2008 Forums

Transact-SQL (2008)

Individual sorting on select statements w/unionall

Author

Topic

adilgunja01
Starting Member

2 Posts

Posted - 2012-06-26 : 13:45:56

Hello,

I have the following query and I want to sort records for each select, individually:

select top(5) a from b
union all
select top(5) a from c
union all
select top(5) a from d

I want to have the first 5 records for the first select statement ordered, then the 2nd 5 records for the 2nd select, then the 3rd...similar to this
1,2,3,4,5,1,2,3,4,5,1,2,3,4,5

Can this be done?

Thanks,
Adil

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-26 : 14:17:25

something like this maybe?

SELECT a
FROM
(
	select top(5) a, 1 AS SortCol from b
	union all
	select top(5) a, 2 AS SortCol from c
	union all
	select top(5) a, 3 AS SortCol from d
) AS T
ORDER BY SortCol, a

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-06-26 : 14:35:24

probably makes no difference but you don't need the derived table for that solution - just the sort column:


select top(5) a, 1 as sortCol from b
union all
select top(5) a, 2 from c
union all
select top(5) a, 3 from d
order by sortCol, a

Be One with the Optimizer
TG

adilgunja01
Starting Member

2 Posts

Posted - 2012-06-27 : 18:28:30

Thanks for your help. Pretty simple solution actually.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources