Solved

Show the highest score from a distinct user mysql

Posted on 2013-06-03
11
545 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
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 16

Expert Comment

by:Brian Pringle
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Misread question
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 41

Expert Comment

by:ralmada
Comment Utility
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
Comment Utility
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 51

Expert Comment

by:Julian Hansen
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
Thanks ralmada, that's working now.

Cheers,

Chris
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
As an example please see this link to demonstrate the query

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

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

743 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now