| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-11 : 10:07:07
|
| Ash writes "I'm building a ranking application to rank players on games they have been playing. I have intRank, intGamesPlayed and intGamesWon fields. Plugging intGamesPlayed and intGamesWon into a formula I made gives me a value determining a rank relative to everyone in the table, eg. 0.534. I store these formula results into intRank, and I can sort descending on intRank (higher values are better ranks) to determine the top N ranks of players.However, the trouble lies in determining an individual player's rank. I could just do what I said before, and select the players from the table sorted descending by rank value, and keep a counter until i read reach the individual's data. The final value of the counter is the player's rank. But users will want their rank position frequently, and I do not want to do this every time (since it's being done on the server side and sent over the network to the client).The best solution for me would be to run a script that would generate a 1,2,3...n rank value for intRank based on the results from my formula. If I wrote the results from my formula into intRank (or another field), can I run another script to sort these values descending and write to intRank a 1,2,3...n value?" |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-06-12 : 01:15:35
|
| hi ashActually, since player's rankings can change at any time (presumably), by definition you're going to have to rebuild the rankings either every time a player does something, or every time they ask for their ranking.You should write an sp which will return a players current rankings. Unless you've got 100000 players, this should be blindingly fast..something like this:create procedure getrank @userID intascreate table #a (ranking int identity(1,1), UserId int) insert into #a (UserID) select UserIDfrom table1 order by ... (use your formula here) DESCselect ranking from #a where UserId = @UserIddrop table #aGOHTH--I hope that when I die someone will say of me "That guy sure owed me a lot of money"Edited by - rrb on 06/12/2002 01:16:40 |
 |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-06-12 : 02:32:07
|
| hmmm...that was yum...yep that'll all work too. Merkin, costwise - what'you reckon?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-06-12 : 02:39:43
|
quote:
Merkin, costwise - what'you reckon?
ummmmYou can buy me a beer when you are in Sydney. That should cover it  Damian |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-06-12 : 02:55:06
|
| On serious note , AFAIK Anythign which involves correlated query's would be a performance killer. test, the result with some thousand records it will hog the peformance.Merkin, did u met nr?-------------------------What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-06-12 : 03:37:24
|
| I think my solution is faster in worst case, but slower for best case...thoughts? (beer aside - but not for long cos it'll go flat and we wouldn't want that would we nr?)Edited by - rrb on 06/12/2002 03:41:05 |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-06-12 : 03:46:32
|
| I would think the solution to the readers challenge would be faster that you think. It was quick for Tim's 10 000 rows.Anywhere you don't have to create a temp table is usually pretty quick No I didn't catch NR in Sydney. He didn't narrow it down to less than 2 places, and I have no idea what he looks like. So I didn't go looking. Maybe next time.Damian |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-06-12 : 03:50:02
|
Nope you're right - I was including the time to display the result in QA....nr's solution is 10X quicker...damn! Oh well - time for a beerEdited by - rrb on 06/12/2002 03:53:54 |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-06-12 : 03:56:05
|
 I just went back and re-read the requirement.....Yes, your way is a good one. The only place it would fall down is where you have equally ranked people and you want to skip a number i.e.12245668etcWhich is where you need to get a bit funkier with it.Damian |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-06-12 : 03:57:36
|
ROFL!There I go changing my story and agreeing with you and you edit your post and make me look like an idiot  Damn youMmmm Beer, good planDamian |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-06-12 : 04:00:14
|
Yeah well - I'm already used to that. It's  o'clock...Edited by - rrb on 06/12/2002 04:01:07 |
|
|
|
|
Ranking data