SQL- Getting maximum value along with all other columns?

Question

I have a table, which can be seen as a evaluation of two courses in several classroom tests, like this:

student_ID     Evaluation           Course1   Course2
------------------------------------------------------
1                   5                 88         93
2                   4                 70         87
1                   5                 93         90
2                   5                 99         91
3                   3                 65         60
3                   4                 88         70

I need to get the result of the Evaluation=5 for each student, if any. If that student has more than one Evaluation=5, the query only show any one of them. So for the above example table, the query result will be

student_ID     Evaluation           Course1   Course2
------------------------------------------------------
1                   5                 88         93
2                   5                 99         91

Of course in my real table, the "Courses" number is more than 2.

Thanks for the help.

John Woo · Accepted Answer · 2013-09-29 20:51:00Z

2

Since you only want to get only one record for every student_id, you can use ROW_NUMBER() which generates sequential number. The number generated will always starts with 1 which you can use to filter out row for every partition, in this case Student_ID.

SELECT  Student_ID, Evaluation, Course1, Course2
FROM    
        (
            SELECT  Student_ID, Evaluation, Course1, Course2,
                    ROW_NUMBER() OVER (PARTITION BY Student_ID 
                    ORDER BY Student_ID) rn
            FROM    TableName
            WHERE   Evaluation = 5
        ) a
WHERE   a.rn = 1

SQLFiddle Demo

answered Sep 29, 2013 at 20:51

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1816353 Over a year ago

Oh my god! You are so quick. Thanks a lot.

Collectives™ on Stack Overflow

SQL- Getting maximum value along with all other columns?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related