MySQL optimized query

Question

Now I came across an issue, I have table as below:

Task Id       StudentId    Marks
  1                1         12
  1                2         15              
  1                3         17
  2                1         15 
  2                3         20
  3                1         8 
  3                3         5         
  3                5         17

I was required to get a lists of studentid who has max and marks in each task. So the required output is:

TaskId StudentId Marks

 1                    3            17
 2                    3            20
 3                    5            17

Now I created a temporary table to store max.marks of each task.

TaskId Max.Marks

 1     17
 2     20
 3     17

Then I joined the temporary table to the main table and fetched the row with marks equal to max.marks of that task.

Can anyone suggest a better query without any sub-queries and temporary table?

Thanks

Answer 1

Instead of temporary table, use a subquery (a derived table), then join it to the original marks table:

SELECT
    t.TaskId, t.StudentId, t.Marks 
FROM
        tableX AS t
    JOIN 
        ( SELECT 
              TaskID
            , MAX(Marks) AS MaxMarks
          FROM 
              tableX
          GROUP BY
              TaskID
        ) AS m
            ON  m.TaskId = t.TaskId
            AND m.MaxMarks = t.Marks ;

If you have an index on (TaskId, Marks, StudentId), it will help efficiency.

You can also use this variation:

SELECT
    t.TaskId, t.StudentId, t.Marks 
FROM
        ( SELECT DISTINCT
              TaskId
          FROM
              tableX
        ) AS dt
    JOIN 
        tableX AS t
            ON  t.TaskId = dt.TaskId
            AND t.Marks = 
                ( SELECT 
                      tm.Marks 
                  FROM 
                      tableX AS tm
                  WHERE
                      tm.TaskId = dt.TaskId
                  ORDER BY 
                      tm.Marks DESC
                  LIMIT 1
                ) ;

Answer 2

I think you can obtain the above result set with below query.. Just have a try..

Select taskId, studentId, max(marks) as marks from tableName group by taskId