Take the 2-minute tour ×
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.

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

share|improve this question

migrated from serverfault.com Jul 14 '12 at 11:25

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

1 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
                ) ;
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 think..it is more efficient then using subqueries..am i wrong?? –  matang Jul 15 '12 at 20:40
    
@matang: Did you test/measure performance? –  ypercube Jul 15 '12 at 23:31

Your Answer

 
discard

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.