where there is 2 tables:
book for example
x
id || --
1 ||
2 ||
3 ||
and loan for example:
id | booId | duration
1 | 1 | 4
2 | 1 | 6
3 | 1 | **5 (last one for book1)**
4 | 2 | 2
5 | 2 | 3
6 | 3 | 8
7 | 3 | **(5 not the last one so i don't need book3)**
8 | 3 | 6
9 | 2 | **5 (last one for book2)**
I need to get all books id , where the last loan duration equals , so in this example it will be : [1,2]
Advertisement
Answer
One method uses aggregation:
select book_id
from loans l
group by book_id
having max(id) = max(case when duration = 5 then id end);
This checks that the last id for a given book matches the last id for a given book with the duration you want.