Order by - 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

General SQL Server Forums

New to SQL Server Programming

Order by

Author

Topic

Kotti
Posting Yak Master

129 Posts

Posted - 2008-12-08 : 00:36:12

Hi friends

I am using two tables member and service .All the user details will be in member table(Userid,name,rating ) and those members who got the service will be stored in service table(His userid will be stored ).

Member table
userid name rating
1 kotti 3.0
2 kumar 4.0
3 kishore 5.0
4 kiran 2.0
5 sam 4.0
6 John 5.0

Service table
serviceid Userid
1 2
2 1

when some searches like this '%k%'

The result should be
userid name rating
2 kumar 4.0
1 kotti 3.0
3 kishore 5.0
4 kiran 2.0

how i get the result means

first i need to dispay the user details of service table order by rating in member table,then other users in member table order by rating

please help me on this

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-12-08 : 01:31:53

I dont have sql avail to test, but something like:

select m.userid, m.name, m.rating
from dbo.Member m
left
join dbo.Rating r on
     m.memberid = r.memberid
order
by   case when r.memberid is not null then 1 else 0 end desc, m.rating desc

Do you have SQL 2005?

Nathan Skerl

chrianth
Yak Posting Veteran

50 Posts

Posted - 2008-12-08 : 01:47:07

try this...

select (case when b.Userid is null then a.Userid else b.Userid end) Userid,
a.[name], a.rating
from Member a
left join Service b
on (a.userid = b.userid)
order by a.rating, isnull(b.userid,0),a.userid desc

Kotti
Posting Yak Master

129 Posts

Posted - 2008-12-08 : 01:52:19

Thank you nathans

Your query is working fine.
Once again i thank you.

Kotti
Posting Yak Master

129 Posts

Posted - 2008-12-08 : 01:56:17

chrianth

Thank you for your reply.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources