Hi guys i have a home work from school that i should show student with the highest score & the registrant id is even, sorry i can't show the structre of table because i don't know how to put them but i have 3 table

1. student
2. user
3. score

i have coding like this

SELECT student.id_student, name, role, status, no_phone, exam_status, score FROM student
JOIN user ON user.username = student.id_student
JOIN score ON score.id_student = student.id_student
WHERE mod(student.id_student, 2) = 0
ORDER BY score DESC

the result for registrant id is work, they show the registrant id even, but the scores show from the highest to the lowest, it should be show the highest only.

i want to use MAX but i dont know where can i put them. can you guys help me fix this, thank you.

CodePudding user response：

If you just want the max score per student you can aggregate the rows before joining:

select s.id_student, u.name, u.role, s.status, u.no_phone, s.exam_status, sc.score
from student s
join user u on u.username = s.id_student
join (
  select Max(score) as score, id_student
    from score
    group id_student
)sc on sc.id_student = s.id_student
where mod(s.id_student, 2) = 0
order by sc.score desc;

Note using short aliases for your tables improves the readability of the query; I guessed at which tables the columns are from - using aliases when joining tables means others, and you, don't need to guess.