How to obtain MAX value and its column header from only 1 row in a table?

Question

Basically I have a sample table:

Town          V1     V2      V3      V4      V5
New York    0.12    0.1    0.11     0.21    -0.12

From a sample query:

SELECT town, v1, v2, v3, v4, v5 FROM sample

I would like a query to return just the following:

New York V4 0.21

Which means, I would like to return town, header of the max value (V4) and max value (0.21)

Is there any easy way of doing it in MySQL?

`SELECT GREATEST(v1, v2, v3, v4, v5)` will return the greatest value. Not sure how to get the header to return with it. — Redbeard011010, Dec 27 '15 at 19:11

score 0 · Answer 1 · answered Dec 27 '15 at 19:18

If you have no NULL values, then:

select town, greatest(v1, v2, v3, v4, v5)

This is more painful if you do have NULL values. If you know the values are never negative:

select town,
       nullif(greatest(coalesce(v1, -1), coalesce(v2, -1), coalesce(v3, -1),
                       coalesce(v4, -1), coalesce(v5, -1)), -1)

How to obtain MAX value and its column header from only 1 row in a table?

1 Answers1