How to add a column to a query result when a query contains an aggregate function?

Question

I have a table called Attendance, which is used to record the time students attend classes. This table has 4 columns, for example 'id', 'course_id', 'attendance_time' and 'student_name'. An example of several entries in this table:

23 100 1/1/2010 10:00 a.m. Tom </p>

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

     

.....

I want to create a summary of the last time I visited for each course. I created a request below:

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

The result is 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 above result. How to do it?

-

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

, . , .

Answer 1

" ", "-n--" " ", . Qaru , , . :

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

, course_id, _time. , . , , course_id, attendance_time .

Answer 2

? , . , . max , , :

SELECT course_id, max(attendance_time) FROM attendance GROUP BY course_id **WHERE course_id = your_id_here**;

, "id", :

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

, :

CREATE VIEW max_course_times AS SELECT course_id AS id, max(attendance_time) FROM attendance GROUP BY course_id;

SELECT * FROM max_course_times;