Find highest value in row and echo number and column name

Question

After too many hours I can't find the answer to this.

First my table

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

How do I find the highest value in the row and show colomun name.

three - 23

[Normalize your data](http://stackoverflow.com/questions/246701/what-is-normalisation-or-normalization-why-is-it-important) — YXD, Jun 22 '11 at 09:44
Your data is not normalized according to what you want to do - you can't easily PIVOT in MySQL. — Konerak, Jun 22 '11 at 09:44
possible duplicate of [MySQL greatest value in row?](http://stackoverflow.com/questions/2687256/mysql-greatest-value-in-row) — Shakti Singh, Jun 22 '11 at 09:46

score 3 · Accepted Answer · answered Jun 22 '11 at 09:53

3

 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

answered Jun 22 '11 at 09:53

Michael Pakhantsov

24,855
6
60
59

ive just tried this and i constantly get this as my result "Resource id #3". What does this mean. Column 3? ive checked mysql table and column one has the highest number. Im confused – Darren Jun 22 '11 at 11:09
in my haste of confusion i forgot to thank every one for their reply. So thanks guys, its truly amazing that strangers are willing to help each other. Mankind is good – Darren Jun 22 '11 at 12:12
Michael just wanted to say a extra thanks to you. All seems to be working. I still get "Resorcue id#3", not sure what that means but it doesnt matter. Echoing $row seems to give me the coloum name. I can carry on with the rest of my code now. Thankyou so much. – Darren Jun 22 '11 at 13:11
Thank you! This post just saved me days of headbashing! – Magictallguy Dec 17 '14 at 11:55

score 1 · Answer 2 · edited May 23 '17 at 11:44

1

I think you should do:

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

this is just if you don't want to look here: MySQL greatest value in row? (i adapted the answer FROM THAT POST to fit your needs if you have problems, anyway refer to that post)

edited May 23 '17 at 11:44

Community

1
1

answered Jun 22 '11 at 09:50

Nicola Peluchetti

76,206
31
145
192

Why are you posting other's answer? This is copied from the link posted in comments above. – Daric Jun 22 '11 at 10:08
That's exactly what i wrote!(i adapted the answer to fit your needs means 'if you don't want to adapt the answer from the other answer i do it for you, i thought i was clear) – Nicola Peluchetti Jun 22 '11 at 10:14

Find highest value in row and echo number and column name

2 Answers2