Sort a Moodle sql pivot query to return highest value for one row

I am using the SQL query below to retrieve some quiz data from a Moodle database.

My test data in the database is:

As you can see, there are TWO rows containing a result for grade_code 148 (100% and 20%) and ONE row containing a result for grade_code 414 (96%).

The MS SQL Server SQL query that gives me the above test data is:

    SELECT
        u.firstname AS 'first name',
        u.lastname AS 'surname',
        u.idnumber AS 'id number',
        gi.idnumber AS 'grade_code',
        CASE gg.finalgrade
          WHEN NULL THEN 'No result'
          ELSE CAST(CAST(ROUND(((gg.finalgrade/gi.grademax)*100),0) AS INTEGER) AS NVARCHAR(10)) + '%'
        END AS 'result'
    FROM mdl_grade_grades AS gg
    INNER JOIN mdl_grade_items gi ON gg.itemid = gi.id
    INNER JOIN mdl_user u ON gg.userid = u.id
    WHERE gi.idnumber IN (
      '148','414'
    )
    AND u.idnumber = '4432433'

When I pivot the above SQL, like this:

    SELECT * FROM (
        SELECT
            u.firstname AS 'first name',
            u.lastname AS 'surname',
            u.idnumber AS 'id number',
            gi.idnumber AS 'grade_code',
            CASE gg.finalgrade
              WHEN NULL THEN 'No result'
              ELSE CAST(CAST(ROUND(((gg.finalgrade/gi.grademax)*100),0) AS INTEGER) AS NVARCHAR(10)) + '%'
            END AS 'result'
        FROM mdl_grade_grades AS gg
        INNER JOIN mdl_grade_items gi ON gg.itemid = gi.id
        INNER JOIN mdl_user u ON gg.userid = u.id
        WHERE gi.idnumber IN (
          '148','414'
        )
        AND u.idnumber = '4432433'
    ) SOURCE
    PIVOT (
        MAX(grade)
        FOR grade_code IN (
          [148],[414]
        )
    ) PIVT

the output result I get is:

The problem is that it is returning the row with 20% for grade_code 148. I want it to return the HIGHEST grade_code (100%), for each grade_code like this:

So, when there are multiple rows for each grade_code the query must return the one row with the highest value for that grade_code.

Is the fix for this just to find a way to ORDER BY the grade_code column?

My first guess was to add a ORDER BY '148','414' to the end of the pivot query, but that is not valid.

Nor is this valid: ORDER BY [148],[414]

Or this: ORDER BY grade_code

What do I do to fix this? How to I always return the one highest result for each grade_code?

You should perform your grade calculations and pivot operations using a numeric type (not text), leaving the calculated score as calculated (finalgrade/grademax) in the range 0..1. The percentage scaling (x 100) and formatting is best deferred to the presentation layer. or in this case, the final select list since there is no presentation layer.

The null case can be handled using the ISNULL() function.

SELECT
    PIVT.[first name], PIVT.surname, PIVT.[id number],
    ISNULL(CAST(CAST(ROUND(PIVT.[148] * 100, 0) AS INTEGER) AS NVARCHAR(10)) + '%',
           'No result') AS [148],
    ISNULL(CAST(CAST(ROUND(PIVT.[414] * 100, 0) AS INTEGER) AS NVARCHAR(10)) + '%',
           'No result') AS [414]
FROM (
    SELECT
        u.firstname AS [first name],
        u.lastname AS surname,
        u.idnumber AS [id number],
        gi.idnumber AS [grade_code],
        CAST(gg.finalgrade AS FLOAT) / gi.grademax AS fractional_grade
    FROM mdl_grade_grades AS gg
    INNER JOIN mdl_grade_items gi ON gg.itemid = gi.id
    INNER JOIN mdl_user u ON gg.userid = u.id
    WHERE gi.idnumber IN (
      '148','414'
    )
    AND u.idnumber = '4432433'
) SOURCE
PIVOT (
    MAX(fractional_grade)
    FOR grade_code IN (
      [148],[414]
    )
) PIVT

Because I was unsure about your source data types, I added a cast to float before the division in the above calculation.

Results (using some reverse engineered test data):

See this db<>fiddle for a demo.