CODEWITHSUNDEEP
mysql
proprius
proprius

Reputation: 522

maximum value row wise in mysql

I have 5 columns in my mysql table:

name s1 s2 s3 s4
A    1   2  3  4
B    15  6  7  8

I want to calculate the column name and value which holds the maximum value out of s1, s2, s3, s4 within a row, i.e.,

name col value
A    s4  4
B    s1  15

I can't use max because it calculates column wise maximum. How am I supposed to do this?

PS: Schema cannot be changed because in all the other use cases, I have to display information name wise.

Thanks in advance.

Upvotes: 2

Views: 749

Answers (2)

fthiella
fthiella

Reputation: 49089

The correct data structure for your table would be something like this:

name | column | value
-----|--------|------
A    | s1     | 1
A    | s2     | 2
A    | s3     | 3
A    | s4     | 4
B    | s1     | 15
B    | s2     | 6
B    | s3     | 7
B    | s4     | 8

and then your query would be like this:

select *
from tablename
where (name, value) in (select name, max(value)
                        from tablename
                        group by name)

but how do you want to manage a tie (two columns with the same maximum value?)

If you cannot change your data structure there is still something you can do, getting the max value it's easy:

select name, greatest(s1, s2, s3, s4)
from tablename

but getting also the column name is a little tricky:

select
  name,
  max_value,
  case when p=1 then 's1'
       when p=2 then 's2'
       when p=3 then 's3'
       when p=4 then 's4'
  end as col_name
from (
select
  name,
  greatest(s1, s2, s3, s4) as max_value,
  field(greatest(s1, s2, s3, s4), s1, s2, s3, s4) as p
from
  tablename
) s

or this:

select
  name,
  greatest(s1, s2, s3, s4) as max_value,
  substring_index(substring_index('s1,s2,s3,s4', ',', field(greatest(s1, s2, s3, s4), s1, s2, s3, s4)), ',', -1) as p
from

Upvotes: 4

user3600910
user3600910

Reputation: 3109

you can use this , but notice that in this case if you got the max value in two different columns you will get null values if you want to get other value we can change this in the case

select name,
       case when s1>s2 and s1>s3 and s1>s4 then 's1'
            when s2>s1 and s2>s3 and s2>s4 then 's2'
            when s3>s1 and s3>s2 and s3>s4 then 's3'
            when s4>s1 and s1>s2 and s1>s3 then 's4' as col
       case when s1>s2 and s1>s3 and s1>s4 then s1
            when s2>s1 and s2>s3 and s2>s4 then s2
            when s3>s1 and s3>s2 and s3>s4 then s3
            when s4>s1 and s1>s2 and s1>s3 then s4 as value
from my_table

Upvotes: 0

Related Questions