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
                  GROUP BY company
                  HAVING max(value) <= 20)

Solution 2:

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

select company
,      sum(case when value < 20 then 1 else 0 end) s
,      count(*) c
from   data
where  year in (2000, 2001, 2002)
group
by     company
having s = c

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