klaus klaus - 2 months ago 8
MySQL Question

Select from table after a cel value

I want to select from MySQL table all records after last value.
This is an example

MyTable

+--------------------+--------------------+--------------------+--------------------+
| date (DATE) |Product_id (INT fk)| Quantite (INT) |type (ENUM('S', 'R')|
+--------------------+--------------------+--------------------+--------------------+
| 2016-09-20 | 1 | 15 | R |
| 2016-09-21 | 1 | 20 | S |
| 2016-09-23 | 1 | 19 | R |
| 2016-09-28 | 1 | 30 | S |
| 2016-09-30 | 1 | 20 | S |
+--------------------+--------------------+--------------------+--------------------+


i want a query to get the last rows with a type S

this is the output i need

+--------------------+--------------------+--------------------+--------------------+
| date (DATE) |Product_id (INT fk)| Quantite (INT) |type (ENUM('S', 'R')|
+--------------------+--------------------+--------------------+--------------------+
| 2016-09-28 | 1 | 30 | S |
| 2016-09-30 | 1 | 20 | S |
+--------------------+--------------------+--------------------+--------------------+

Answer

Simply use LIMIT :

SELECT *
FROM YourTable t
WHERE t.type = 'S'
ORDER BY t.date DESC
LIMIT 1;

If you want it for each ID then you can use NOT EXISTS() :

SELECT * FROM YourTable t
WHERE NOT EXISTS (SELECT 1 FROM YourTable s
                  WHERE s.product_id = t.product_id
                    AND s.type = 'S'
                    AND s.date > t.date)
  AND t.type = 'S'

EDIT: If I understood you correctly, you want all the record with type S after the last record of type R , if so :

SELECT * FROM YourTable t
WHERE NOT EXISTS (SELECT 1 FROM YourTable s
                  WHERE s.product_id = t.product_id
                    AND s.type = 'R'
                    AND s.date > t.date)
  AND t.type = 'S'
Comments