mysql - Select pair of column where max condition

Question

I have structure like below:

column1, column2, column3, pair1, pair2, pair3
  100      200      150     1      2       3

I need to select a column with a maximum value with a pair of it, in this case: 200, 2

Answer 1

You can use a case expression:

select greatest(column1, column2, column3) as column,
       (case greatest(column1, column2, column3)
            when column1 then pair1
            when column2 then pair2
            when column3 then pair3
        end) as pair

Note: You should probably fix the data model. Having such "paired" columns is a bad idea. You should have a separate table with one row per pair.