How to combine GROUP BY and JOIN?

Question

MySQL

I work with PhpMyAdmin. I have a log_taking table whose columns look like this:

id | reader_id | book_id | taken_at | returned_at

There is a table readers:

id | last_name | first_name

(id if any are different and not the same)

And the books table:

id | name | pub_date

I want to pull a table where each id from log_taking will have exactly one row with the earliest taken_at date.
(For example, if I have id = 101 - there are 5 such records, similarly, 5 records with id = 201, 301, 401, 501, then I must display a table of 5 lines (1 line from each id)

) log_taking tables, I think it would be correct to do this:

SELECT book_id, MAX(taken_at) FROM log_taking GROUP BY book_id

But it is not possible to use JOIN to hook the first_name, last_name, pub_date and returned_at fields to it.
Help please

PS I tried like this, but it's wrong

spoiler

SELECT
log_taking.book_id,
log_taking.returned_at,
log_taking.taken_at,
readers.first_name,
readers.last_name,
readers.pub_date
FROM
log_taking JOIN
(
SELECT book_id, MAX(taken_at)
FROM log_taking
GROUP BY book_id
) books
ON log_taking.book_id = books.id
JOIN readers
ON readers.id = log_taking.reader_id

Answer 1

To find readers with the most recently taken books, use the row_number window function to number the rows within each reader (partitioning by reader id) in descending order by taken_at date. From the result, take the rows that received the numbers 1.

select * from
(select r.id as reader_id, r.first_name, r.last_name, b.id book_id, b.name, b.pub_date, row_number() over (partition by r.id order by l.taken_at desc) as rownum
from readers r
left join log_taking l on r.id = l.reader_id
left join books b on b.id = l.book_id
) a
where a.rownum = 1

PS: A bunch of left join will display all readers, regardless of whether they took books or not. A simple join will give you a list of readers who have borrowed books at least once.
PPS: Window functions are available from MySQL version 8.
If version 5, then you have to fence the bike with user variables.