SQL to get last record when using group by statement

Question

I want to get last record of "productID" using group by statement.

The database record:

cartID  productID   customerID  quantity    dateAdded   paidStatus
1       20          14          1           2019-03-14  1
2       21          14          1           2019-04-24  1
7       20          16          1           2019-04-25  1
8       20          17          1           2019-04-29  1
11      21          17          2           2019-07-28  1

What I tried:SQL

SELECT * FROM `cart` WHERE productID=20 OR productID=21 GROUP BY productID ORDER BY cartID ASC

For current result I get is the first record of cartID 1 not 8

The SQL record at below:

cartID  productID   customerID  quantity    dateAdded   paidStatus
1       20          14          1           2019-03-14  1
2       21          14          1           2019-04-24  1

What I wants: For some reason I need to get multiple record(by productID) example like WHERE productID=20 OR WHERE productID=21

productID 20 and 21 each having multiple record but

I need to get the both of last record

The result I needed:

cartID  productID   customerID  quantity    dateAdded   paidStatus
8       20          17          1           2019-04-29  1
11      21          17          2           2019-07-28  1

Answer 1

You could use a subquery for max value group by custome and product and join

select  a.productId, a.CustomerId, a.quantity, a.dateadded , a.paidstatus 
from my_table a 
inner join  (
  select productId, CustomerId, max(dateadded) max_date
  from my_table  
  group by  productId, CustomerId

) t on t.productId = a.productId
        and t.CustomerId = a.CustomerId
            and t.max_date  = a.dateadded

Answer 2

With NOT EXISTS:

SELECT t.* 
FROM cart t
WHERE t.productID IN (20, 21)
AND NOT EXISTS (
  SELECT 1 FROM cart
  WHERE productID = t.productID AND dateAdded > t.dateAdded
)