SQL help: choose the last 3 comments for EVERY student?

Question

I have two tables for storing student data for the classroom:

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

The database is used to store daily comments about student behavior, and sometimes the teacher makes several comments about the student on a particular day.

Now let's say that the teacher wants to get a list of the last 3 comments made for EVERY student, for example:

Discussion Jessica 7/1/09
Jessica 7/1/09 skips notes
Jessica 5/3/09 is missing
Ciboney 7/2/09 is a lot of participation
Ciboney 4/30/09 is missing
Ciboney 2/22/09 is a lot of participation ... and t .d. for the whole class

A single SQL query should return a set of comments for each student to eliminate the person’s need for intensive consumption for the teacher to run separate queries for each student in the class.

I know that this is similar to SQL statement help - select the last order for each customer , but I need to display the last 3 records for each person, I can’t figure out how to get from here to

Thanks for your suggestions!

Answer 1

A slightly modified solution from this article on my blog:

• Analytical 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

group_concat .

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