selecting once - 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 2005 Forums

Transact-SQL (2005)

selecting once

Author

Topic

eddyuk
Starting Member

4 Posts

Posted - 2010-10-15 : 12:19:27

hello.
i have a table that contains users scores log. Now i must select top 10 players who did the highest score.

this is the query i use:
select
distinct(userid),
max(score) as score,
logged
from
LFO_FlashGamesUsersScores
group by
userid,
logged
order by
score desc

this is the result:
userid score logged
-----------------------------------------
6420 39 2010-10-10 13:30:19.263
6420 39 2010-10-10 13:35:51.890
6420 39 2010-10-10 14:15:21.983
6420 39 2010-10-10 14:12:21.013
1176 39 2010-10-10 09:12:20.200
1176 39 2010-10-10 09:16:37.747
1176 39 2010-10-10 09:19:39.497
6420 39 2010-10-09 13:08:50.347
6420 39 2010-10-09 13:52:05.907
6420 39 2010-10-09 13:58:17.643

The problem is that it selects user multiple times. How do i make user appear only once with his highest score.

thanks in advanced.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-10-15 : 15:31:32

Try this:


SELECT TOP(10) userid, score, logged
  FROM (SELECT T.userid, T.score, T.logged,
               ROW_NUMBER() OVER(PARTITION BY T.userid
                                     ORDER BY T.score DESC) AS row_num
          FROM LFO_FlashGamesUsersScores AS T) AS T
 WHERE T.row_num = 1
 ORDER BY score DESC;

eddyuk
Starting Member

4 Posts

Posted - 2010-10-16 : 09:48:24

That worked perfectly my friend :) thanks a lot, you saved that day!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources