CODEWITHSUNDEEP
mysql
adammo
adammo

Reputation: 211

selecting only newest row with specific value

Table:

person | borrow_date  | is_borrowed | SN | date        | id
1      | 2019-01-10...| 1           | 20 |2019-01-10...| 6
3      | 2019-01-09...| 3           | 10 |2019-01-09...| 5
1      | 2019-01-08...| 1           | 10 |2019-01-08...| 4
2      | 2019-01-08...| 1           | 10 |2019-01-08...| 3
1      | NULL         | 2           | 20 |2019-01-07...| 2
1      | NULL         | 2           | 10 |2019-01-07...| 1

My wanted output is to select newest rows where "is_borrowed" equals 1 and grouped by SN, so that when the query is executed with person=2 or person=3 then it would retrieve empty set. Whereas for person=1 it would give back two rows. Wanted output (where person=1):

person | borrow_date  | is_borrowed | SN | date         |id
1      | 2019-01-10...| 1           | 20 | 2019-01-10...|6
1      | 2019-01-08...| 1           | 10 | 2019-01-08...|4

Wanted output (where person=2):

EMPTY SET

Wanted output (where person=3):

EMPTY SET

This is my current query and it sadly doesn't work.

SELECT a.SN, a.is_borrowed,a.max(date) as date, a.person
FROM table a
INNER JOIN (SELECT SN, MAX(date) as date, osoba from table where person like 
"2"  group by SN) as b
ON a.SN=b.SN and a.date=b.date
WHERE a.person like "2" and a.is_borrowed=1

Upvotes: 1

Views: 50

Answers (1)

Paul Karam
Paul Karam

Reputation: 4210

If I correctly understood you from the question and the comment you made under it, here's one way to do it without specifying the person:

select * 
from TableName as p 
inner join (select max(borrow_date) as borrow_date,
                   SN 
            FROM TableName
            where is_borrowed = 1 
            group by SN) as p2
on p.borrow_date = p2.borrow_date and p.SN = p2.SN

This should give you the result you're looking for. Here's a demo.

Note that I had to change the borrowed_date values in the table since yours contain hours and minutes while I didn't add those.

You can always specify it for each person by adding a where clause after the join.

select p.person, 
       p.borrow_date, 
       p.is_borrowed, 
       p.SN, 
       p.date, 
       p.id 
from TableName as p 
inner join (select max(borrow_date) as borrow_date,
                   SN 
            FROM TableName
            where is_borrowed = 1 
            group by SN) as p2
on p.borrow_date = p2.borrow_date and p.SN = p2.SN
where p.person = '1'

Output: 

person | borrow_date  | is_borrowed | SN |  date        | id
1      |  2019-01-10  |     1       | 20 |  2019-01-10  | 6   
1      |  2019-01-08  |     1       | 10 |  2019-01-08  | 4

While where p.person = '2' and where p.person = '3' will return empty sets.

Upvotes: 1

Related Questions