Ranking of users scores - 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

Transact-SQL (2000)

Ranking of users scores

Author

Topic

TGarmon
Starting Member

18 Posts

Posted - 2002-04-18 : 09:51:28

I have a table that I would like to average users' scores and rank their average scores. I would like to do this using a store procedure since I have over a 1,000,000 scores. Any ideas?

Thanks,
John

Nazim
A custom title

1408 Posts

Posted - 2002-04-18 : 10:18:44

thought the II reader's challene would help you .

you can get a fair idea on ranking follow this http://www.sqlteam.com/item.asp?ItemID=6398 .

--------------------------------------------------------------

jongregg
Starting Member

31 Posts

Posted - 2002-04-18 : 10:18:47

Without seeing the table structure, would something like this work:

SELECT
user_id,
avg(user_score)
FROM
score_table
GROUP BY
user_id
ORDER BY
avg(user_score) DESC

This could be easily converted into a stored procedure.

Jon
-----------

After thinking about it, it doesn't give you a ranking number only a descending order

. The link above is probably the way to go.

Edited by - jongregg on 04/18/2002 10:22:17

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-18 : 10:30:40

Whether the self-join methods are viable is more dependent on number of users than the number of scores. Still, with 1×10^6 scores, it's unlikely you've got a small number of users. A temporary table with an identity column or a cursor are probably the way to go.

Edited by - Arnold Fribble on 04/18/2002 10:31:27

jongregg
Starting Member

31 Posts

Posted - 2002-04-18 : 10:38:52

quote:
A temporary table with an identity column or a cursor are probably the way to go.

Did you just say cursor?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-18 : 12:53:47

Sorry, my eyes were dilated this morning (for diagnostic purposes, I hasten to add) and the sun must have gotten to my brain. Feeling better now

Edited by - Arnold Fribble on 04/18/2002 12:55:22

jongregg
Starting Member

31 Posts

Posted - 2002-04-18 : 15:30:26

Forgiven

Just wondering, do you know of any situations where a c****r is the only option and if so, should you seriously reconsider a re-design?

Nazim
A custom title

1408 Posts

Posted - 2002-04-18 : 15:32:46

Found this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6195 Check the last post by Kheon .

--------------------------------------------------------------

TGarmon
Starting Member

18 Posts

Posted - 2002-04-19 : 08:01:20

The first link from above almost gave me what I wanted. This is what I have:

select dq1.c1_1,
dq1.score_T,
Rank = (select count(*) + 1
from dataquestionnaire dq2
where dq2.score_T > dq1.score_T)
from dataquestionnaire dq1
order by Rank

But there is one last problem. I might have ten entries for the same user (c1_1). I want to average his score and rank him compare to the others. I only have 200,000 users, but I am getting back a million rows. Any more ideas?

Thanks so much for you help,
John

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources