CODEWITHSUNDEEP
mysqldatabaseselect
stockwet
stockwet

Reputation: 75

MySQL: Get all rows if specific value reached after date

In MySQL, I want to find all the rows where the value of a column went to 0 after a specific date.

So, given the data:

cat    value     date
a      95        2015-09-01
a      78        2015-10-01
a      0         2015-11-01
a      0         2015-12-01
b      129       2015-09-01
b      230       2015-10-01
b      201       2015-11-01
b      140       2015-12-01

In this case, I want to run a query that asks:

Which categories have 0 value after 10/1/2015 and had a positive value before 11/1/2015?

The result should show category "a".

I suspect is a nested select statement, but haven't quite figured it out.

Upvotes: 3

Views: 77

Answers (2)

Nir Levy
Nir Levy

Reputation: 12953

select * from yourTable where value = 0 and date > '2015-10-01' and cat in (
  select distinct cat where value > 0 and date < '2015-11-1'
)

Explanation: you can split the query to two parts - the inner query with the in statement is in charge of getting the cat ID's that were positive before 11/1/15, and the where value = 0 and date > '2015-10-01' will give you those that are 0 after 10/1/15

Upvotes: 2

Cooper Gillan
Cooper Gillan

Reputation: 86

Building off what Nir Levy said, if you only want the category to be returned, you can select just the distinct values for cat:

SELECT DISTINCT cat
FROM stack_test.your_table
WHERE value = 0 AND date > '2015-10-01' AND
cat IN (SELECT cat FROM stack_test.mysql_rows WHERE value > 0 AND date < '2015-11-01');

Upvotes: 1

Related Questions