How to 'add' a column to a query result while the query contains aggregate function?

Question

I have a table named 'Attendance' which is used to record student attendance time in courses. This table has 4 columns, say 'id', 'course_id', 'attendance_time', and 'student_name'. An example of few records in this table is:

23    100    1/1/2010 10:00:00    Tom

24    100    1/1/2010 10:20:00    Bob

25    187    1/2/2010 08:01:01    Lisa

.....

I want to create a summary of the latest attendance time for each course. I created a query below:

SELECT course_id, max(attendance_time) FROM attendance GROUP BY course_id

The result would be something like this

100    1/1/2010 10:20:00

187    1/2/2010 08:01:01

Now, all I want to do is add the 'id' column to the result above. How to do it?

I can't just change the command to something like this

SELECT id, course_id, max(attendance_time) FROM attendance GROUP BY id, course_id

because it would return all the records as if the aggregate function is not used. Please help me.

Answer 1

This is a typical 'greatest per group', 'greatest-n-per-group' or 'groupwise maximum' query that comes up on Stack Overflow almost every day. You can search Stack Overflow for these terms to find many different examples of how to solve this with different databases. One way to solve it is as follows:

SELECT
    T2.course_id,
    T2.attendance_time
    T2.id
FROM (
    SELECT
        course_id,
        MAX(attendance_time) AS attendance_time
    FROM attendance
    GROUP BY course_id
) T1
JOIN attendance T2
ON T1.course_id = T2.course_id
AND T1.attendance_time = T2.attendance_time

Note that this query can in theory return multiple rows per course_id if there are multiple rows with the same attendance_time. If that cannot happen then you don't need to worry about this issue. If this is a potential problem then you can solve this by adding an extra grouping on course_id, attendance_time and selecting the minimum or maximum id.