Select Top Score or Winners of game

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 2008 Forums

Transact-SQL (2008)

Select Top Score or Winners of game

Author

Topic

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-05-02 : 05:39:11

I have a table that records the scores of players in a game. I want to create a statement that picks the winner.

SELECT Top(1) [GameID]
,[PlayerUserName]
,[PlayerScore]
FROM [Players]
ORDER BY PlayerScore

This would work however if there are two players with the same score I need to select both of them as winners. How can I do this?

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-05-02 : 06:15:16

select ...
from Players
where PlayerScore = (select max(PlayerScore) from Players)

No, you're never too old to Yak'n'Roll if you're too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-02 : 07:06:01

You can also use WITH TIES keyword

SELECT Top(1) WITH TIES [GameID]
,[PlayerUserName]
,[PlayerScore]
FROM [Players]
ORDER BY PlayerScore

Unless this is golf, you may want to add a DESC keyword in the order by clause (to pick the highest score rather than the lowest).

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-05-02 : 07:14:43

Many thanks for that.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2012-05-02 : 10:20:12

Another option:

select * from
	(
	SELECT 
	*, RANK() OVER (ORDER BY PlayerScore) [rank]
	from Players
	) T
where [RANK] = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources