MySQL – How to Find Highest Value of a Row

maxMySQL

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

Best Answer

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;

Related Solutions

Mysql Join to get multiple row value from

Perhaps something like this:

SELECT e.employee_id, e.first_name, e.last_name,
CONCAT(addr.address_one,',',addr.address_two,',',state) fulladdr,
CONCAT(ph.mobile',',ph.mobile_one,',',ph.phone) phonenumbers,
e.dob, e.maritial_id, e.sex_id, e.photo, s.sex_label
FROM employee e
INNER JOIN sex s ON e.sex_id = s.id
INNER JOIN address addr ON e.address_id = addr.id
INNER JOIN phone ph ON e.phone_id = ph.id;

Mysql – Find the second highest value from a subquery or derived table

Write your join properly and just limit the result set after sorting if it's enough for you to get the second value, no matter if there are two or more courses with the same sum of coursefees. If you need to consider this, it becomes more complicated. Just let me know...

SELECT SUM(coursefees) AS sum_coursefees
FROM enrollment e INNER JOIN batch b ON e.batchid = b.batchid
INNER JOIN course c ON b.courseid = c.courseid
GROUP BY c.courseid
ORDER BY sum_coursefees DESC
LIMIT 1 OFFSET 1;

UPDATE:

That should do it:

SELECT * FROM (
  SELECT 
  c.courseid, 
  @rank := IF(@prev = SUM(coursefees), @rank, @rank + 1) AS rank,
  @prev := SUM(coursefees) AS sum_coursefees
  FROM enrollment e 
  INNER JOIN batch b ON e.batchid = b.batchid
  INNER JOIN course c ON b.courseid = c.courseid
  , (SELECT @rank:=0, @prev:=NULL) var_init
  GROUP BY c.courseid
  ORDER BY sum_coursefees DESC
) sq
WHERE rank = 2;

Note though, that the order in the select clause is important. Do not switch columns.

Best Answer

Related Solutions

Mysql Join to get multiple row value from

Mysql – Find the second highest value from a subquery or derived table

Related Question