?
Solved

Show the highest score from a distinct user mysql

Posted on 2013-06-03
11
Medium Priority
?
592 Views
Last Modified: 2013-06-03
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
Comment
Question by:djfenom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 16

Expert Comment

by:Brian Pringle
ID: 39216251
Have you tried using the MAX() function on just that one table to make sure that it is working correctly?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 39216272
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
 

Author Comment

by:djfenom
ID: 39216304
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 58

Expert Comment

by:Julian Hansen
ID: 39216322
Misread question
0
 
LVL 58

Expert Comment

by:Julian Hansen
ID: 39216336
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
 
LVL 41

Expert Comment

by:ralmada
ID: 39216357
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
 

Author Comment

by:djfenom
ID: 39216406
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
 
LVL 58

Expert Comment

by:Julian Hansen
ID: 39216468
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
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 39216472
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
 

Author Comment

by:djfenom
ID: 39216501
Thanks ralmada, that's working now.

Cheers,

Chris
0
 
LVL 58

Expert Comment

by:Julian Hansen
ID: 39216515
As an example please see this link to demonstrate the query

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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question