Oder by a particular value

Question

Oder by a particular value

malavika.ramanathan

SSCrazy

Points: 2790
More actions
May 15, 2010 at 12:47 am

#146943

Hi All,
I have written a query like this:
select 0 as rid,'ALL' as UserName
union
select rid,UserName
from hc_users where locationid in (@locid)
ORDER BY 'ALL', UserName
My intention is to fix that ALL as first and sort the other names in ascending order.
but this is giving error.
please help

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Malavika, without seeing you data we cant actually write a positively superior code, buy judging what u want from the code u put up, this might help you

select 0 as rid,'ALL' as UserName

from hc_users where locationid in (@locid)

union

select rid,UserName

from hc_users where locationid in (@locid)

ORDER BY UserName

This will put "ALL" users first in the selected result set and then appends the ASC Usernames

Please post sample data for your table, we will give you a superior code for you!

Cheers! 😎

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

Here's what i would do

select 0 as rid,'ALL' as UserName, 0 as OrderCol

union all

select rid,UserName,1

from hc_users where locationid in (@locid)

ORDER BY 3,2

Clear Sky SQL
My Blog[/url]

malavika.ramanathan SSCrazy Points: 2790 More actions · Answer 3

hi all,

thanks for your replies....

i got the desired o/p . but i am not very clear about choosing one more column after UserName...

can you pls explain that?

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 4

Ive added the OrderCol columns simply to use in the order by clause.

I could of equally said

select 0 as rid,'ALL' as UserName, 0 as OrderCol

union all

select rid,UserName,1

from hc_users

where locationid in (@locid)

ORDER BY OrderCol,UserName

if that makes thing clearer.

Remember that the Order by clause will 'execute' after ( ie the result of) the union and so therefore order the entire resultset.

Clear Sky SQL
My Blog[/url]