sql - mysql - Get two greatest values from multiple columns -

we can use greatest greatest value multiple columns below

select greatest(mark1,mark2,mark3,mark4,mark5) best_mark marks 

but want 2 best marks all(5) marks.

can on mysql query?

table structure (i know wrong - created someone):

student_id  | name | mark1 | mark2 | mark3 | mark4 | mark5 

this not elegant solution if cannot alter table structure can unpivot data , apply user defined variable row number each student_id. code similar following:

select student_id, name, col, data (   select student_id, name, col,     data,     @rn:=case when student_id = @prev @rn else 0 end +1 rn,     @prev:=student_id      (     select student_id, name, col,       @rn,        @prev,       case s.col         when 'mark1' mark1         when 'mark2' mark2         when 'mark3' mark3         when 'mark4' mark4         when 'mark5' mark5       end data     marks     cross join      (       select 'mark1' col union        select 'mark2' union        select 'mark3' union        select 'mark4' union        select 'mark5'     ) s     cross join (select @rn := 0, @prev:=0) c   ) s   order student_id, data desc ) d rn <= 2 order student_id, data desc; 

see sql fiddle demo. return top 2 marks per student_id. inner subquery performing similar function using union unpivot not querying against table multiple times result.