Solved

Show the highest score from a distinct user mysql

Posted on 2013-06-03
11
559 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
 
LVL 52

Expert Comment

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

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
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.

 
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 52

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 52

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

932 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

7 Experts available now in Live!

Get 1:1 Help Now