CODEWITHSUNDEEP
mysqlsql
Logan
Logan

Reputation: 1694

mysql - Get two greatest values from multiple columns

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

Upvotes: 9

Views: 2170

Answers (4)

Taryn
Taryn

Reputation: 247630

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.

Upvotes: 2

silkfire
silkfire

Reputation: 25935

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

Upvotes: 0

ajt
ajt

Reputation: 552

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

Upvotes: 0

silkfire
silkfire

Reputation: 25935

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:

  1. 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.
  2. Change your marks table to hold 3 columns: id, mark/grade/value, mark_type (this column foreign constraints to mark_types).
  3. Write your SELECT query with the help of joins, and GROUP BY mark_type.

Upvotes: 2

Related Questions