mysql - Get two greatest values from multiple columns

Question

We can use GREATEST to get greatest value from multiple columns like below

SELECT GREATEST(mark1,mark2,mark3,mark4,mark5) AS best_mark FROM marks

But now I want to get two best marks from all(5) marks.

Can I do this on mysql query?

Table structure (I know it is wrong - created by someone):

student_id  | Name | mark1 | mark2 | mark3 | mark4 | mark5

Answer 1

This is not the most elegant solution but if you cannot alter the table structure then you can unpivot the data and then apply a user defined variable to get a row number for each student_id. The code will be similar to the following:

select student_id, name, col, data
from
(
  SELECT student_id, name, col,
    data,
    @rn:=case when student_id = @prev then @rn else 0 end +1 rn,
    @prev:=student_id
  FROM 
  (
    SELECT student_id, name, col,
      @rn, 
      @prev,
      CASE s.col
        WHEN 'mark1' THEN mark1
        WHEN 'mark2' THEN mark2
        WHEN 'mark3' THEN mark3
        WHEN 'mark4' THEN mark4
        WHEN 'mark5' THEN mark5
      END AS DATA
    FROM marks
    CROSS JOIN 
    (
      SELECT 'mark1' AS col UNION ALL 
      SELECT 'mark2' UNION ALL 
      SELECT 'mark3' UNION ALL 
      SELECT 'mark4' UNION ALL 
      SELECT 'mark5'
    ) s
    cross join (select @rn := 0, @prev:=0) c
  ) s
  order by student_id, data desc
) d
where rn <= 2
order by student_id, data desc;

See SQL Fiddle with Demo. This will return the top 2 marks per student_id. The inner subquery is performing a similar function as using a UNION ALL to unpivot but you are not querying against the table multiple times to get the result.