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.
| Author |
Topic |
|
demione
Starting Member
2 Posts |
 Posted - 2008-07-31 : 21:57:53
|
| I have a SQL 2000 table which contains users who have played a game. One particular column of this table contains the user's top score. I originally wanted to display a top 10 list of the best scores. But, then I got thinking, this would be bad if there were 10 guys who were absolute experts who dominate the charts, and never let anybody else show up on the list. So, what I thought about doing was have a sliding list. In other words, show the user what rank at which he exists on the overall chart, plus the scores of some players who have scores around where he's at.For example, let's say there are 10,000 user records in the database, the best score in the database is 1,000,000, and the lowest score is 10. If my best score is 500,000, then I'd like to be able to know that i'm 5,096th on the list, and I'd like to see the scores from users #5,092 through #5,101.So what I need here is a SQL 2000 query which returns my user record, plus four user records who have the nearest higher scores, and five user records that have the nearest lower scores. Altogether this will provide the sliding 'top 10 near me' list.Is this possible to do without choking the DB? I'm not nearly good enough at SQL to understand how to approach this challenge efficiently.Thanks! |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-08-01 : 01:34:16
|
| Something like below. I haven't tested it at all byt the way. I also can't remember if WITH TIES is SQL 2000 or not. Your requirements didn't specify what to do with ties so you can take that out. select *,(select count(*) from table where score<x.score)+1 as place from(select top 5 * with ties from table where score>(select score from table where id=@myid) order by score union all select top 5 with ties * from table where score<=(select score from table where id=@myid) order by score desc) x order by score |
 |
|
|
demione
Starting Member
2 Posts |
Posted - 2008-08-01 : 03:39:37
|
| Thanks bud! Yep, WITH TIES has been in SQL Server since v7. The query you provided worked about half correctly. The only main problem I saw was that i needed to manually select 'score' and not * because it gave me the following error: "ORDER BY items must appear in the select list if the statement contains a UNION operator."2) When I corrected the above to see what would happen, it gave me the following results:score place -------------------- ----------- 0 10 10 10 180085 4724231592 58081561293 84412500153 89713145953 920217506773 10343 |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-08-03 : 10:06:28
|
| Sorry mate. Without data & table structures I can only make suggestions. I can't see why that won't work. |
|
|
|
|
|
|
|
|
Selecting ranked records that are close to my own