SQL: Select TOP N Marks Per User (In a List of Users)

Question

I am having difficulty writing a Stored Procedure that will query a list of students with their associative marks.

Retrieving a List of Students - trivial Retrieving the top five marks per student - trivial...SELECT TOP (5) * WHERE StudentID = X

Combining these two, I am a bit confused.

I would like the Stored Procedure to return two tables:

• First table that lists students by a criteria
• Second table: a list of grades (5 each per student in the First Table)

Second table is when it is tricky. I can get all the marks per student in First Table but not sure how I can limit it to top 5.

Answer 1

Try to avoid a stored procedure that returns 2 result sets. Make this 2 separate procedures.

TOP 5 returns random rows if you don't specify a sort order. I assume you want to sort by grade. You will need some additional sort order criteria in case the top grades are all the same. You could return the most recent top 5 grades, for instance.

For your 2nd procedure to return the top 5 grades per student, look into the syntax of the RANK clause.

Answer 2

If you're using SQL 2005 or greater, this should work. If not, there are other slightly messier ways to do it.

WITH Student_Grades AS
(
    SELECT
     S.student_id,
     G.grade,
     RANK() OVER (PARTITION BY S.student_id, ORDER BY G.exam_date DESC) AS grade_rank
    FROM
     Students S
    LEFT OUTER JOIN Grades G ON
     G.student_id = S.student_id
)
SELECT
    student_id,
    grade
FROM
    Student_Grades
WHERE
    grade_rank <= 5

Answer 3

LOL @ "B. Because other databases (Orcale) can't do it"

Um, duh, he is not using "Orcale" obviously!!

LOL!!!!