How to get max value from a MySQL Database-CodePudding

I have an MYSQL database table of student info and their test scores per subject and I am trying to fetch each student's highest score in all subjects using the SQL query below

SELECT DISTINCT
    first_name,
    last_name,
    subject_id,
    (SELECT 
            MAX(score)
        FROM
            cbt_attempts_tbl
        WHERE
            first_name = first_name) AS MAX_SCORE
FROM
    cbt_attempts_tbl
WHERE
    score IS NOT NULL
ORDER BY first_name DESC

sample data

After running the query, the result I get are not as expected

CodePudding user response：

In your subselect you need to link both tables, for example by using an alias

SELECT DISTINCT
    first_name,
    last_name,
    subject_id,
    (SELECT 
            MAX(score)
        FROM
            cbt_attempts_tbl
        WHERE
            first_name = f1.first_name) AS MAX_SCORE
FROM
    cbt_attempts_tbl f1
WHERE
    score IS NOT NULL
ORDER BY first_name DESC

But I don't really know what you are searching for, but following give the same, as DISTINCT doesn't make sense as long you don't have multiple rows with the same subject_id and name combination

SELECT 
    first_name,
    last_name,
    subject_id,
    (SELECT 
            MAX(score)
        FROM
            cbt_attempts_tbl
        WHERE
            first_name = f1.first_name) AS MAX_SCORE
FROM
    cbt_attempts_tbl f1
WHERE
    score IS NOT NULL
GROUP BY first_name,
    last_name,
    subject_id
ORDER BY first_name DESC

CodePudding user response：

If you are not worried about students without any score , then your query would be like

SELECT first_name, last_name, subject_id, MAX(score) FROM cbt_attempts_tbl GROUP BY first_name, last_name, subject_id ORDER BY first_name DESC

Otherwise, if you want to keep a full student list, you need to make a left joined query like

SELECT DISTINCT t.first_name, t.last_name, scores.subject_id, scores.subject_max_score FROM cbt_attempts_tbl t LEFT JOIN (SELECT first_name, last_name, subject_id, MAX(score) AS subject_max_score FROM cbt_attempts_tbl GROUP BY first_name, last_name, subject_id) scores ON scores.first_name = t.first_name AND scores.last_name = t.last_name GROUP BY t.first_name, t.last_name, scores.subject_id ORDER BY t.first_name DESC

At last, if to be more comprehensive, your database design is not correct. You should have a students table and your cbt_attempts_tbl table should have a foreign key student_id that references a students table id column. And then it would be completely different query - select from students left joined by cbt_attempts_tbl on foreign key condition