2

I searched and found many results using "Grep highest value of a row in MySQL" but I don't understand the results.

I have a row which primary key value is 4 and there are another 8 values. Those are 45 78 89 54 98 14 19 70. I want to select the highest value, ie 98 from this row. How do I do this ?

My database table name is class6A

table with sample data

3

Use the GREATEST() function on all the columns

SELECT
GREATEST
(bangla_1st,bangla_2nd,english_1st,english_2nd,math,social_science,religious,science)
HighestValue
FROM class6A
WHERE roll = 4;

If any of the columns are NULL, then make the NULL the value 0

SELECT
GREATEST
(
    IFNULL(bangla_1st,0),
    IFNULL(bangla_2nd,0),
    IFNULL(english_1st,0),
    IFNULL(english_2nd,0),
    IFNULL(math,0),
    IFNULL(social_science,0),
    IFNULL(religious,0),
    IFNULL(science,0)
) HighestValue
FROM class6A
WHERE roll = 4;

GIVE IT A TRY !!!

In the future, you may add another subject to or remove a subject from the student record.

Here is another method using Dynamic SQL that figures out the subjects present in the table

SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(CONCAT('IFNULL(',column_name,',0)')) INTO @grades
FROM information_schema.columns
WHERE table_schema=DATABASE() AND table_name='class6A'
AND column_name NOT IN ('roll','student_name');
SET @sql = CONCAT('SELECT GREATEST(',@grades,') HighestValue FROM class6A WHERE roll=4');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.