Php / MYSQL Highscore列表仅显示他/她的电子邮件问题的最高分数

Question

I would like my select statement only to print out the highest score of every submission from one email.

Table : scorelist

name
score
email
createdAT

I have try following query:

SELECT DISTINCT name, score FROM scorelist ORDER BY score DESC, createdAt ASC

This is what I've tried. I think I need a WHERE clause where I ask that email=email = limit 1 but it doesn't work as intended.

The query prints out every instance but not the duplicated scores so if a user have had the same score it's been removed by using DISTINCT of course.

Answer 1

If you want highest score according email, Then use group by clause with MAX(). try below query:

SELECT name, MAX(score) as score FROM scorelist 
GROUP BY email ORDER BY score DESC, createdAt ASC;