CODEWITHSUNDEEP
sqlpostgresql
psrpsrpsr
psrpsrpsr

Reputation: 457

How can I return all rows for a value where at least one row meets a condition?

I'm trying to return all rows for IDs where one or more Num_Occurrence rows is >=10.

Here is an example of the raw data:

+------+-----------+----------------+
| ID   | YearMonth | Num_Occurrence |
+------+-----------+----------------+
| 1234 | 201601    | 7              |
+------+-----------+----------------+
| 1234 | 201602    | 8              |
+------+-----------+----------------+
| 1234 | 201603    | 12             |
+------+-----------+----------------+
| 1234 | 201604    | 9              |
+------+-----------+----------------+
| 9898 | 201601    | 9              |
+------+-----------+----------------+
| 9898 | 201602    | 8              |
+------+-----------+----------------+
| 9898 | 201603    | 9              |
+------+-----------+----------------+
| 9898 | 201604    | 6              |
+------+-----------+----------------+

And here is the desired output:

+------+-----------+----------------+
| ID   | YearMonth | Num_Occurrence |
+------+-----------+----------------+
| 1234 | 201601    | 7              |
+------+-----------+----------------+
| 1234 | 201602    | 8              |
+------+-----------+----------------+
| 1234 | 201603    | 12             |
+------+-----------+----------------+
| 1234 | 201604    | 9              |
+------+-----------+----------------+

I understand that the following will not work:

SELECT *
FROM tbl
WHERE Num_Occurrence >= 10

Because that would only return this row:

+------+-----------+----------------+
| ID   | YearMonth | Num_Occurrence |
+------+-----------+----------------+
| 1234 | 201603    | 12             |
+------+-----------+----------------+

As aforementioned, I need to return ALL rows for that ANY ID where Num_Occurrence is >=10.

Thanks!!

Upvotes: 0

Views: 100

Answers (2)

Graham Penrose
Graham Penrose

Reputation: 477

SELECT * FROM [tbl] t1
WHERE EXISTS (SELECT * FROM [tbl] t2
              WHERE t2.ID = t1.id
              AND t2.Num_Occurrence >= 10);

The "EXISTS" clause here uses a sub-query to find all rows having Num_Occurrence >= 10, then compares that against the complete table to get all rows with a matching ID.

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1271041

You can do this as:

select t.*
from tbl t
where exists (select 1
              from tbl t2
              where t2.id = t.id and t2.id >= 10
             );

Upvotes: 2

Related Questions