Geek Answers Handbook

Find the highest value in the number of rows and echo numbers and column name

Too many hours I can not find the answer to this question.

My table first

  id | one | two | three | four | five | galname
-------------------------------------------------
  1  |  2  |  5  |   23  |  4   | 5    |  Bob

How to find the maximum value in a string and show the name colomun.

three - 23
+5
source share
2 answers
 select  id,  GREATEST(one, two, three, four, five) value,
        case GREATEST(one, two, three, four, five)
         when one then 'one'
         when two then 'two'
         when three then 'three'
         when four then 'four'
         when five then 'five' end column_name
 from your_table
+3
source

I think you should:

SELECT CASE GREATEST(`id`,`one`, `two`, `three`, `four`, `five`)
         WHEN `id` THEN `id`
         WHEN `one` THEN `one`
         WHEN `two` THEN `two`
         WHEN `three` THEN `three`
         WHEN `four` THEN `four`
         WHEN `five` THEN `five`
         ELSE 0
      END AS maxcol,
      GREATEST(`id`,`one`, `two`, `three`, `four`, `five`) as maxvalue 
    FROM tbl

it is simple if you do not want to look here: The greatest MySQL value in a row? (I adapted the FROM THAT POST answer to suit your needs, if you have problems, refer to this post anyway)

+1
source

More articles:


All Articles