Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?


share|improve this question

migrated from Jul 14 '12 at 11:25

This question came from our site for system and network administrators.

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

    t.TaskId, t.StudentId, t.Marks 
        tableX AS t
        ( SELECT 
            , MAX(Marks) AS MaxMarks
          GROUP BY
        ) 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:

    t.TaskId, t.StudentId, t.Marks 
        ) AS dt
        tableX AS t
            ON  t.TaskId = dt.TaskId
            AND t.Marks = 
                ( SELECT 
                      tableX AS tm
                      tm.TaskId = dt.TaskId
                  ORDER BY 
                      tm.Marks DESC
                  LIMIT 1
                ) ;
share|improve this answer
+1 for your answer, but he specifically asked for one without subqueries. I'm not sure that's possible. I am removing my answer, since i misread the question. – Aaron Brown Jul 14 '12 at 12:54
@Aaron: I don't think this can be done without subqueries of some kind. (perhaps with triggers that update another table that holds these rseults). And I don't really see any reason for not using subqueries - unless it's a theoretical question. – ypercubeᵀᴹ Jul 14 '12 at 13:16
I agree, I was only pointing out the poster's requirement. – Aaron Brown Jul 14 '12 at 13:45
hi guys thanks for the comments..but..i used the temporary table bcos..i is more efficient then using i wrong?? – matang Jul 15 '12 at 20:40
@matang: Did you test/measure performance? – ypercubeᵀᴹ Jul 15 '12 at 23:31

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.