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

SQL Server Development (2000)

Row Order

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-07-07 : 02:33:10

I have 20,000 records in my table and have a query that pulls 1 record according to the userID passed to the SP. My question is this :

I want to pull a record from the table. I have a field named "points", when I pull this record is it possible to also return a value that represents the ranking of the "points" value in the 20,000 records. As if I did a "ORDER BY POINTS DESC" on the whole table?

Thanks alot,

Mike

macka
Posting Yak Master

162 Posts

Posted - 2002-07-07 : 07:21:24

create proc spRankPoints
@userid integer
as

create table #worktable(rank integer identity,userid integer)

insert into #worktable(userid)
select userid
from sometable
order by points desc

select sometable.*,#worktable.rank
from sometable
inner join #worktable on sometable.userid = #worktable.userid
where sometable.userid = @userID

drop table #worktable

macka.

Edited by - macka on 07/08/2002 02:27:27

nr
SQLTeam MVY

12543 Posts

Posted - 2002-07-07 : 19:56:53

select * ,rank = (select count(*) + 1 from tbl t2 where t2.points > tbl.points)
from tbl
where userid = @userid

==========================================
Cursors are useful if you don't know sql.
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