Query items from a history table

I have a “library” history table (Excel like) to see where a book was or is.

id	date	book	action	client
1	2020-12-01	1	toClient	1
2	2020-12-02	1	returned	1
3	2020-12-03	1	toClient	2
4	2020-12-04	2	toClient	2
5	2020-12-05	3	toClient	1
6	2020-12-06	3	returned	1
7	2020-12-07	2	returned	2
8	2020-12-08	2	toClient	1

I am trying to discover how many books and what books are by client 2 (for example).

I have something like that to find everything about the books that are or was by client 2:

SELECT * FROM history WHERE book IN 
  (SELECT book FROM history WHERE action='toClient' AND client=2 GROUP BY book)

But so, I get also books that aren’t anymore on client 2.

Is there a simple way to query items where the last action was “toClient” and not “returned” and the current client is client 2?

Edit: forgot to write that action can be also revision, timeExtension or something other.

Answer

To list books that are currently out, you can do:

select h.*
from history h
where 
    action = 'toClient' 
    and h.date = (select max(d1.date) from history h1 where h1.book = h.book)

The idea is to filter on the latest event date per book. Then we only retain the row if the corresponding event is “toClient”.

This returns the results for all clients. If you want just one client, just add an additional condition to the where clause of the query:

and client = 2

You can also do this with window functions:

select *
from (
    select h.*,
        rank() over(partition by book order by date desc) rn
    from history h
) h
where rn = 1 and action = 'toClient' -- and client = 2