How to SELECT DISTINCT of one field and other fields From INNER JOIN table
I use PHP and 开发者_如何转开发MySQL and I have 2 tables:
- table score: have fields 'user_id' and 'score'
- table user: have fields 'user_id' and 'user_name'
People play game then store user_id and score in table 'score' every time game was ended. There are many rows for a single user in table score.
What I want? I want to select top 10 players with distinct user_name to show on the list, so what is the correct sql? Code below is now my current sql, but it's not show result what I want.
SELECT DISTINCT * FROM score as t1
INNER JOIN user AS t2 ON t1.user_id=t2.user_id
WHERE score>0
ORDER BY t1.score DESC
LIMIT 10
What is the mistake for this?
does this work? (not tested)
select username, max(score) as maxscore
from score, user
where score.userid=user.userid and score>0
group by username
order by maxscore desc
The following sql will return the top 10 highest scoring players sorted by their best score (not a top 10 of scores, but a top 10 of players).
SELECT
u.user_name,
max(s.score)
FROM
score as s
INNER JOIN [user] AS u
ON s.user_id = u.user_id
WHERE
score > 0
GROUP BY
u.user_name
ORDER BY
2 DESC
LIMIT 10
精彩评论