Get column of a mysql entry

Question

Is there a possibility to get the name of the column a database entry belongs to?

Perhaps I have three columns with column names col1, col2 and col3. Now I want to select for every column the column with the maximum entry, something like this.

Select name_of_column(max(col1,col2,col3)).

I know that I can ask for the name of the columns by its ordinal position in the information_schema.COLUMNS table but how do I get the ordinal position of a database entry within a table?

Answer 1

This will show you the columns from a table

SHOW COLUMNS FROM 'table';

You'll then have to iterate through the results in a scripting language.

Answer 2

If you want to implement it in a pure SQL, you can use CASE statement and an additional variable:

 SELECT @m := GREATEST(col1, col2), 
 CASE @m 
      WHEN col1 THEN 'col1' 
      WHEN col2 THEN 'col2' 
 END 
 FROM my_table

Answer 3

You could do this:

select
    case true
        when col1 > col2 and col1 > col3 then 'col1'
        when col2 > col1 and col2 > col3 then 'col2'
        when col3 > col1 and col3 > col2 then 'col3'
    end 
from
    mytable

But what if the maximum value appears in multiple columns?