Order by a particular value - 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)

Order by a particular value

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2010-05-15 : 02:26:58

Hi All,

I want to order by a particular field.... i.e i want the 'ALL' to be always on top... and other values to be ordered by asc....

i wrote this:

select 0 as rid,'ALL' as UserName
union
select rid,UserName
from hc_users where locationid in (@locid)
order by 'ALL',Username

but this gave error saying a constant cannot be passed in ORDER BY...

can someone help?

thanks in adv.....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-15 : 03:05:21

[code]
select rid,UserName
from
(
select 0 as rid,'ALL' as UserName,0 AS Ord
union
select rid,UserName,1 As Ord
from hc_users where locationid in (@locid)
)t
order by Ord,Username
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mrm23
Posting Yak Master

198 Posts

Posted - 2010-05-15 : 03:13:39

Hi visakh,
thanks for the reply. this i am using for report.....
i know this is not the forum for ques on report, but it is an urgent issue so i am asking here.

as you know by now, i have an ALL option in my report parameter.
but it is a multi-valued parameter and hence i will be getting select all option by default.
if the user selects 'Select All' then my ALL option will also be selected and this is causing error.

i understood we cannot hide or disable that select all. how else can i handle this?

i need both 'Select All' and 'All' to work in the same way....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-15 : 03:50:43

if you already have select all why should need an extra ALL option?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mrm23
Posting Yak Master

198 Posts

Posted - 2010-05-15 : 04:05:00

actually my ALL option is the default. earlier i had not added this. but the reequirement is such that by default the report must run for all users and all clients. if the user doesnt want to see every data, then he can select from the drop down.

for this i added that all....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-15 : 04:38:34

http://blogs.msdn.com/bimusings/archive/2007/05/07/how-do-you-set-select-all-as-the-default-for-multi-value-parameters-in-reporting-services.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources