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

http://stackoverflow.com/questions/5360894/get-the-second-highest-value-in-a-mysql-table — Andy Thompson, May 21 '13 at 11:23
@jWeavers - Sorry, I thought he wanted the top2 for each of mark1-5 not the best out of the five. — Andy Thompson, May 21 '13 at 11:31

silkfire · Answer 1 · 2013-05-21T11:29:29.073

I think you should change your database structure, because having that many marks horizontally (i.e. as fields/columns) already means you're doing something wrong.

Instead put all your marks in a separate table where you create a many to many relationship and then perform the necessary SELECT together with LIMIT.

Suggestions:

Create a table that you call mark_types. Columns: id, mark_type. I see that you currently have 5 type of marks; it would be very simple to add additional types.
Change your marks table to hold 3 columns: id, mark/grade/value, mark_type (this column foreign constraints to mark_types).
Write your SELECT query with the help of joins, and GROUP BY mark_type.

table structure created by some one so I don't want to interfere on that just I need solution for current table structure. — Logan, May 21 '13 at 11:27

Taryn · Accepted Answer · 2013-05-21T16:19:55.130

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.

I think OP wanted to create a column out of ALL the 5 mark columns, and then pick out the 2 highest ones. Not sure though. My solution reflects this anyway. — silkfire, May 21 '13 at 12:03

score 0 · Answer 3 · edited May 23 '17 at 12:11

0

you can create a temporary table and then

Create a temporary table in a SELECT statement without a separate CREATE TABLE

query that table as follows

SELECT TOP 2 * FROM temp ORDER BY mark DESC

then

drop temp table

edited May 23 '17 at 12:11

Community

1
1

answered May 21 '13 at 11:40

ajt

553
8
25

silkfire · Answer 4 · 2013-05-21T11:48:28.623

Okay here's a new answer that's should work with the current table structure:

SELECT `student_id`, `Name`, `mark` FROM (SELECT `student_id`, `Name`, `mark1` AS `mark` FROM `marks`
                                          UNION ALL
                                          SELECT `student_id`, `Name`, `mark2` AS `mark` FROM `marks`
                                          UNION ALL
                                          SELECT `student_id`, `Name`, `mark3` AS `mark` FROM `marks`
                                          UNION ALL
                                          SELECT `student_id`, `Name`, `mark4` AS `mark` FROM `marks`
                                          UNION ALL
                                          SELECT `student_id`, `Name`, `mark5` AS `mark` FROM `marks`) AS `marks`
ORDER BY `mark` DESC
LIMIT 2

you should also include `student_id` and `name` otherwise.. it won't show correct output. — Ravi, May 21 '13 at 11:49

mysql - Get two greatest values from multiple columns

4 Answers4