Solved

Show the highest score from a distinct user mysql

Posted on 2013-06-03
11
569 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
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 54

Expert Comment

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

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 54

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 500 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 54

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
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…
The viewer will learn how to count occurrences of each item in an array.

773 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