get records that last record binded with it have a specific value

user_7040964 0
Viewed 37

I have this caseenter image description here

where there is 2 tables:

book for example

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]

mysql sql
1 Answers

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.

user_11440350
Related