SQL help: select the last 3 comments for EACH student?

Question

I have two tables to store student data for a grade-school classroom:

Behavior_Log has the columns student_id, comments, date Student_Roster has the columns student_id, firstname, lastname

The database is used to store daily comments about student behavior, and sometimes the teacher makes multiple comments about a student in a given day.

Now let's say the teacher wants to be able to pull up a list of the last 3 comments made for EACH student, like this:

Jessica 7/1/09 talking
Jessica 7/1/09 passing notes
Jessica 5/3/09 absent
Ciboney 7/2/09 great participation
Ciboney 4/30/09 absent
Ciboney 2/22/09 great participation ...and so on for the whole class

The single SQL query must return a set of comments for each student to eliminate the human-time-intensive need for the teacher to run separate queries for each student in the class.

I know that this sounds similar to SQL Statement Help - Select latest Order for each Customer but I need to display the last 3 entries for each person, I can't figure out how to get from here to there.

Thanks for your suggestions!

Answer 1

A slightly modified solution from this article in my blog:

• Analytic functions: SUM, AVG, ROW_NUMBER

 

SELECT  student_id, date, comment
FROM    (
        SELECT  student_id, date, comment, (@r := @r + 1) AS rn
        FROM    (
                SELECT  @_student_id:= -1
                ) vars,
                (
                SELECT  *
                FROM
                        behavior_log a
                ORDER BY
                        student_id, date DESC
                ) ao
        WHERE   CASE WHEN @_student_id <> student_id THEN @r := 0 ELSE 0 END IS NOT NULL
                AND (@_student_id := student_id) IS NOT NULL
        ) sc
JOIN    Student_Roster sr
ON      sr.student_id = sc.student_id
WHERE   rn <= 3

Answer 2

A different approach would be to use the group_concat function and a single sub select and a limit on that subselect.

select (
     select group_concat( concat( student, ', ', date,', ', comment ) separator '\n' )
       from Behavior_Log 
      where student_id = s.student_id
   group by student_id
      limit 3 )
  from Student_Roster s