Select query sorting - 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)

Select query sorting

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-02-12 : 16:16:46

I have the following select: how can i sort the records by lastname:


	SELECT null as userid, null as username
		WHERE @EmptyRow=1
	UNION ALL
	select UA.userID, US.FirstName + ' ' +
		US.LastName AS username 
		from tab_ccsnetUserAccess UA, Tab_ccsnetUsers US
 		where UA.contractid=@ContractID and UA.UserID =
		US.UserId and US.Deleted = 0 and UA.Deleted = 0 
		group by US.LastName,US.FirstName,UA.username,UA.userid

Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-12 : 16:22:49

Contratulations - this must be your first select statement.

use ORDER BY (as the last clause in the statement)

Be One with the Optimizer
TG

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-02-12 : 16:27:53

Hello TG, I forgot i do have Union all:

Here is my query:
SELECT null as userid, null as username
WHERE @EmptyRow=1
UNION ALL
select UA.userID, US.FirstName + ' ' +
US.LastName AS username
from tab_ccsnetUserAccess UA, Tab_ccsnetUsers US
where UA.contractid=@ContractID and UA.UserID =
US.UserId and US.Deleted = 0 and UA.Deleted = 0
group by US.LastName,US.FirstName,UA.username,UA.userid

quote:
Originally posted by TG

Contratulations - this must be your first select statement.

use ORDER BY (as the last clause in the statement)

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-12 : 16:35:39

try this:


select <all columns except LastName>
from   (
       select..., LastName
       from...
       where...
       group by...

       UNION ALL

       select..., LastName
       from...
       where...
       group by...
       ) d
ORDER BY LastName

Be One with the Optimizer
TG

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-02-12 : 16:39:05

Thanks, i think there may be a better way.

quote:

Originally posted by TG

try this:


select <all columns except LastName>
from   (
       select..., LastName
       from...
       where...
       group by...

       UNION ALL

       select..., LastName
       from...
       where...
       group by...
       ) d
ORDER BY LastName

Be One with the Optimizer
TG

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-12 : 16:43:26

quote:
Originally posted by cplusplus

Thanks, i think there may be a better way.

For what >?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-12 : 16:48:14

Well, be sure to let me know if you find one

- because If you have a UNIONed query then you can't order by something that you are not selecting. So unless you want to remove the UNION or include the LastName...

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources