• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 605
  • Last Modified:

Show the highest score from a distinct user mysql

I have a leaderboard page on my website and players can upload their scores to it multiple times. I only want the page to display the player once, with their highest score.

I've tried the following but it only seems to display the first instance of a score added by the player, not the highest.

SELECT leaderboard_scores.*, leaderboard_users.*, MAX(leaderboard_scores.scoreTotal) AS score
		FROM leaderboard_scores
		LEFT JOIN leaderboard_users ON leaderboard_scores.scoreUser = leaderboard_users.userID
		GROUP BY scoreUser
		ORDER BY score ASC

Open in new window


There are 2 tables joined together for the scores and users.

Thanks,

Chris
0
djfenom
Asked:
djfenom
  • 4
  • 3
  • 3
  • +1
1 Solution
 
Brian PringleSystems Analyst II, SCM, ERPCommented:
Have you tried using the MAX() function on just that one table to make sure that it is working correctly?
0
 
ralmadaCommented:
try

SELECT 	b.*, 
	a.*, 
from leaderboard_users a
inner join (select scoreUser, max(scoreTotal) mScoreTotal FROM leaderboard_scores group by ScoreUser) b on a.userID = b.scoreUser

Open in new window

0
 
djfenomAuthor Commented:
I've checked that MAX function is working and it's definitely giving the highest score.

ralmada, should that just work as is? I'm getting the following error:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from leaderboard_users a inner join (select scoreUser, max(scoreTotal) scoreT' at line 3"
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Julian HansenCommented:
Misread question
0
 
Julian HansenCommented:
This should work

SELECT MAX(leaderboard_score.scoreTotal), `leaderboard_users.scoreUser`
FROM leaderboard_users left join leaderboard_scores
on leaderboard_users.userID = leaderboard_scores.scoreUser
GROUP BY `leaderboard_users.userID`;

Without seeing your full schema not possible to say whether this will work - but if it does not then there is something in how you have strucutred yoru tables that is preventing it from working.


On test data here works as expected.
0
 
ralmadaCommented:
extra comma there on the second line:

SELECT 	b.*, 
	a.*
from leaderboard_users a
inner join (select scoreUser, max(scoreTotal) mScoreTotal FROM leaderboard_scores group by ScoreUser) b on a.userID = b.scoreUser

Open in new window

0
 
djfenomAuthor Commented:
julianH, couldn't get your way going.

ralmada, this works, however I have columns for scoreDate and scoreID from the leaderboard_scores table that I need to use also and these aren't showing up?
0
 
Julian HansenCommented:
julianH, couldn't get your way going.

Did it fail or not return the right results.

The query posted works correctly against test data I constructed. If it does not return the right results then we need to see your schema to understand why but the query is correct for the problem stated.
0
 
ralmadaCommented:
ok so try this

SELECT 	b.*, 
	a.*
from leaderboard_users a
inner join (
	select t1.* from leaderboard_scores t1
	where t1.scoreTotal = (select max(scoreTotal) from leaderboard_scores where scoreUser = t1.scoreUser)
) b on a.userID = b.scoreUser

Open in new window


or using left join

SELECT 	b.*, 
	a.*
from leaderboard_users a
left join (
	select t1.* from leaderboard_scores t1
	where t1.scoreTotal = (select max(scoreTotal) from leaderboard_scores where scoreUser = t1.scoreUser)
) b on a.userID = b.scoreUser

Open in new window

0
 
djfenomAuthor Commented:
Thanks ralmada, that's working now.

Cheers,

Chris
0
 
Julian HansenCommented:
As an example please see this link to demonstrate the query

http://www.marcorpsa.com/ee/t251.php
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now