CODEWITHSUNDEEP
sqlmysqldatabase
Sebastian Müller
Sebastian Müller

Reputation: 5589

Get column of a mysql entry

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?

Upvotes: 3

Views: 225

Answers (3)

Vitalii Fedorenko
Vitalii Fedorenko

Reputation: 114420

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

Upvotes: 1

ChristopheD
ChristopheD

Reputation: 116117

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?

Upvotes: 1

Andy
Andy

Reputation: 17771

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.

Upvotes: 1

Related Questions