Select rows with counts less than value for all dates

Question

There is Mysql Ver 15.1 Distrib 5.5.64-MariaDB table:

id  date        count
1   17.10.2021  1
2   17.10.2021  1
3   17.10.2021  2

1   18.10.2021  1
2   18.10.2021  2
3   18.10.2021  3

1   19.10.2021  1
2   19.10.2021  1
3   19.10.2021  4

How to select only that id which has count < 2 for all dates ?

Expected result:

id
1

user_10498828 · Answer

You can do it with aggregation and the condition in the HAVING clause:

SELECT id
FROM tablename
GROUP BY id
HAVING MAX(count) < 2

See the demo.
Results:

id
1

Select rows with counts less than value for all dates

1 Answers