SQL Query : Find max 2 of 3 number and store it in

Question

I have a table named result having column name, rollno, sub1, sub2, sub3, max1, max2 etc. sub1, sub2, sub3 will store marks obtained. I want to find max of sub1, sub2, sub3 and store it in max1 and find second max of sub1, sub2, sub3 and store it in max2.

Example

sub1  sub2  sub3      max1  max2
10     15    20        20    15
40     10    25        40    25
33     64    51        64    51

Can anyone tell me the sql code for this ?

UPDATE

Instead of storing the maximum number to max1 and second maximum to max2, I want to divide maximum number and second maximum number by 2 and want to store it in average column without storing it in max1 and max2.

Means I don't want two extra column max1 and max2 to store maximum and second maximum, then add it and divide it by 2 then store it in average. I want to do average direclty.

Please update the code.

Example

sub1  sub2  sub3   average

10     15    20     17.5   ( (Maximum + Second Maximum)/2 )
40     10    25     32.5   
33     64    51     57.5  

Answer 1

Consider the following

mysql> create table test (sub1 int, sub2 int , sub3 int);
Query OK, 0 rows affected (0.11 sec)

mysql> insert into test values (20,30,40),(10,40,50),(30,10,20);
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test ;
+------+------+------+
| sub1 | sub2 | sub3 |
+------+------+------+
|   20 |   30 |   40 |
|   10 |   40 |   50 |
|   30 |   10 |   20 |
+------+------+------+

So to get the max1 and max2 from the columns you can use greatest function.

select * ,
greatest(sub1,sub2,sub3) as max1 , 
greatest(
 case 
  when greatest(sub1,sub2,sub3) = sub1 then 0 else sub1 
 end,
 case 
  when greatest(sub1,sub2,sub3) = sub2 then  0 else sub2 
 end, 
 case 
  when greatest(sub1,sub2,sub3) = sub3 then 0 else sub3 
 end
) as max2 from test ;

This will give you something as

+------+------+------+------+------+
| sub1 | sub2 | sub3 | max1 | max2 |
+------+------+------+------+------+
|   20 |   30 |   40 |   40 |   30 |
|   10 |   40 |   50 |   50 |   40 |
|   30 |   10 |   20 |   30 |   20 |
+------+------+------+------+------+

You can use this for update command as

update table_name
set 
max1 = greatest(sub1,sub2,sub3),
max2 = greatest(
 case 
  when greatest(sub1,sub2,sub3) = sub1 then 0 else sub1 
 end,
 case 
  when greatest(sub1,sub2,sub3) = sub2 then  0 else sub2 
 end, 
 case 
  when greatest(sub1,sub2,sub3) = sub3 then 0 else sub3 
 end
) 

To get the average of max1 and max2 and update as

update table_name
set 
`average`
= ( 
   greatest(sub1,sub2,sub3)+
   greatest(
    case 
      when greatest(sub1,sub2,sub3) = sub1 then 0 else sub1 
    end,
    case 
      when greatest(sub1,sub2,sub3) = sub2 then  0 else sub2 
    end, 
    case 
      when greatest(sub1,sub2,sub3) = sub3 then 0 else sub3 
    end
     )
)/2 ;