How to get Highest Value MySQL

Question

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

Answer 1

Use MAX with GROUP BY userUserName, it should work!

SELECT
    info_person.userUsername,
    MAX(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"
GROUP BY
    info_person.userUsername
ORDER BY
    info_person.userUsername

Answer 2

with cte as (
SELECT DISTINCT
    info_person.userUsername,
    infoExamResult.resultPoint,
    infoExamResult.resultPercentage,
    infoExamResult.optAnswer,
    ROW_NUMBER() OVER(PARTITION BY info_person.userUsername ORDER BY infoExamResult.resultPoint DESC) AS RNUM 
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"
)
select 
    userUsername,
    resultPoint,
    resultPercentage,
    optAnswer
from cte 
    where RNUM = 1 
    order by userUsername