Get mysql input column

Question

Is it possible to get the name of the column to which the database record belongs?

Maybe I have three columns with column names col1, col2 and col3. Now I want to select the column with the maximum record for each column, something like this.

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

I know that I can query the column name by its ordinal position in the information_schema.COLUMNS table, but how to get the ordinal position of the database record in the table?

Answer 1

If you want to implement it in pure SQL, you can use the 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 2

This will show you the columns from the table

SHOW COLUMNS FROM 'table';

.

Answer 3

You can do it:

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?