Match function for integer in mysql

Question

I have a table structure

 ID   Col_1   col_2  col_3  col_4  max
    1    34     23     45     32   45
    2    20     19     67     18   67 
    3    40     10     76     86   86

I want to derive something like this, the rank column is derived from looking up the "max" column value against columns like "col_1","col_2","col_3","col_4". and it should return me the index or the column number which starts from 1 for col_1 , 2 for col_2 and so on

ID   Col_1   col_2  col_3  col_4  max  rank
    1    34     23     45     32   45    3
    2    20     19     67     18   67    3
    3    40     10     76     86   86    4

this is what I have tried but unable to get the desired output. any help would be appreciated.

 SELECT ID,Col_1,col_2,col_3,col_4,max, match(max) AGAINST(col_1,col_2,col_3,col_4) from demo;

Gordon Linoff Gordon Linoff · Accepted Answer · 2016-07-29T10:43:42

Here is one method, that is rather brute force:

select d.*,
       (case greatest(col_1, col_2, col_3, col_4, col_5)
            when col_1 then 1
            when col_2 then 2
            when col_3 then 3
            when col_4 then 4
            when col_5 then 5
        end) as max_index
from demo d;

As a note: The need to do this type of operation suggests a flawed data model. The column values should probably be stored in a table that has one such value per row.

I should also note that you can do this with field():

select d.*,
       field(greatest(col_1, col_2, col_3, col_4, col_5), 
             col_1, col_2, col_3, col_4, col_5)
from demo d;

This is about the same effort computationally, but it is definitely much shorter in length.

Match function for integer in mysql

1 Answers