Sqlite, Sliding To Get Results Based On Value And Date

This questions is posted on a suggestion in this thread. I'm using SQLite/Database browser and my data looks like this: data.csv company year value A 2000 15 A 2

Solution 1:

You want all companies whose maximum value is no larger than 20:

SELECT*FROM Data
WHERE company IN (SELECT company
                  FROM Data
                  GROUPBY company
                  HAVINGmax(value) <=20)

Solution 2:

Not sure if there are better solutions, but I think this will work:

select company
,      sum(casewhenvalue<20then1else0end) s
,      count(*) c
from   data
whereyearin (2000, 2001, 2002)
groupby     company
having s = c

It will check whether the count equals the number of years where the value is less than 20.