Deepesh Deepesh - 4 months ago 9
SQL Question

Match function for integer in mysql

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;

Answer

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.

Comments