SELECT DISTINCT
info_person.userUsername,
infoExamResult.resultPoint,
infoExamResult.resultPercentage,
infoExamResult.optAnswer
FROM info_person
INNER JOIN infoExamResult ON infoExamResult.personID = info_person.personID
where infoExamResult.datetimeStart BETWEEN "2024-03-18" AND "2024-03-26"
and infoExamResult.optAnswer = "COMMIT"
ORDER BY info_person.userUsername
Outcome
which resultPoint are Score and resultPercentage are Percent of max score
+-------+-----------+------------------+-------------------+-------------------+
| userUsername | resultPoint | ResultPercentage | optAnswer |
+-------+-----------+------------------+-------------------+-------------------+
| NSO001000 | 20 | 50% | COMMIT |
| NSO001000 | 10 | 25% | COMMIT |
| NSO002000 | 40 | 100% | COMMIT |
| NSO002000 | 30 | 75% | COMMIT |
+-------------------+-------------------+------------------+-------------------+
I use Distinct userUsername Already but 1 user has 2 resultPoint I want to get The highest Point
Im really beginner with SQL
And this what i want Look like
What I Looking for
+-------+-----------+------------------+-------------------+-------------------+
| userUsername | resultPoint | ResultPercentage | optAnswer |
+-------+-----------+------------------+-------------------+-------------------+
| NSO001000 | 20 | 50% | COMMIT |
| NSO002000 | 40 | 100% | COMMIT |
+-------------------+-------------------+------------------+-------------------+
I want to get Highest resultpoint but Max Function Can’t get it
2
Answers
Use MAX with GROUP BY userUserName, it should work!