General Query Help - 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

General SQL Server Forums

New to SQL Server Programming

General Query Help

Author

Topic

devoidx
Starting Member

3 Posts

Posted - 2012-09-04 : 09:23:04

Hi! given 2 tables:

users (userid integer, username txt)
scores (userid integer, score integer)

where users.userid is unique (but not necessarily so for scores.userid)

what is the best way to retrieve the username with the highest score in a single query?

I have:
select u.username from users u
where u.userid = (select s.userid from scores s where s.score = (select max(score) from scores))

but I'm not sure that this is the cleanest way to do this. It also fails should there be 2 users with the same highest score.

Thanks in advance for any help

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-04 : 09:32:10

So if a user can have more than one entry in scores:
do you want to SUM the entries and then get the MAX or do really want the MAX without SUM?

Too old to Rock'n'Roll too young to die.

devoidx
Starting Member

3 Posts

Posted - 2012-09-04 : 09:36:39

i want the username with the highest score (max). So say I have:

users:
1,john
2,dave
3,bill

scores:
1,25
2,50
2,100

I want the query to return 'dave'.

if i add an extra row to scores:
3,100

then I want the query to return 'dave' and 'bill'

The query i posted does the first bit (although i don't know if its the best way)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-04 : 10:15:01

This?


select distinct u.username from users u
join
(
 select userid, score
 from scores s
 join (select max(score) as maxScore from scores)t1
  on t1.maxScore = s.score
)dt
on dt.userid = u.userid

Too old to Rock'n'Roll too young to die.

devoidx
Starting Member

3 Posts

Posted - 2012-09-04 : 10:24:51

that is absolutely awesome. thanks!

this was a simplified version of the problem I'm having, now to work on the real one.

Thanks again!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-09-04 : 10:26:29

SELECT TOP 1 with ties u.username
FROM Users u
INNER JOIN scores s on u.userid = s.userid
ORDER BY s.score desc

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources