How can I order my unioned table ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

How can I order my unioned table ?

Author

Topic

WaterWolf
Starting Member

24 Posts

Posted - 2006-07-06 : 11:35:03

Hello,

I have a select statement that selects a username (a string of characters) and other info and unions the result with another select statement that selects an id number instead of the username. I want to order the table so that the usernames come first in alphabetical order followed by the id numbers in numerical order. However it is not possible to use seperate ORDER BY commands in the sql statement once union or union all are used. I can use one ORDER BY command to order the whole resulting table but then it puts the numbers first. Is there a way of ordering the table the way I want ?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2006-07-06 : 12:28:09

I'm not sure if I fully understand what the issue is because you should be able to set up your select list in the UNION to be in any order you want.

But, if you can not change that for some reason you can do a select on the UNIONed table. Something like:

SELECT FirstName, LastName, EmployeeID
FROM
(
	(
		SELECT EmployeeID, FirstName, LastName
		FROM dbo.Employees
	)
	UNION
	(
		SELECT EmployeeID, FirstName, LastName
		FROM dbo.Employees
	)
) a
ORDER BY FirstName

Hopefully that helps. :)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-06 : 12:39:31

Your id number will have to be converted to character otherwise you will get an error so

select username, col from tbl
union
select convert(varchar(20),id), col from tbl

to separate the resultsets add another column to order them
select username, col
from
(
select username, col, srt = 1 from tbl
union
select convert(varchar(20),id), 2, col from tbl
) a
order by srt, username

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources