skip to Main Content
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


  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
    
    Login or Signup to reply.
  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
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search