CODEWITHSUNDEEP
sqldatabasepostgresql
322896
322896

Reputation: 986

SQL query to select rows that based on some value of other rows in the same table

Assume I have the following activity table:

id     type   value   flag
------|------|-------|------|
1     |A     | 13    | 1    |
2     |B     | 29    |      |
3     |C     | 11    |      |
4     |A     | 78    |      |
5     |X     | 91    |      |
6     |C     | 2     |      |
7     |B     | 14    | 1    |

I want to select rows that any row with the same type has the flag 1 or the type is X. In this case, I would like to get:

id     type   value   flag
------|------|-------|------|
1     |A     | 13    | 1    |
4     |A     | 78    |      |
2     |B     | 29    |      |
7     |B     | 14    | 1    |
5     |X     | 91    |      |

I can do an INNER JOIN to get the result like:

SELECT
  "activities".*
FROM "activities"
INNER JOIN activities AS a2
  ON activities.type = a2.type
  AND a2.flag = 1
  OR activities.type = 'X'
  AND activities.id = a2.id

However, this is slow when the amount of records becomes large, especially when I need to do a COUNT(*) on top of the result. I wonder how I can rewrite the query and make it more performant.

I am using Postgres. Thanks!

Upvotes: 1

Views: 66

Answers (2)

valex
valex

Reputation: 24134

Here are 3 ways to do it. Choose one which will be work faster on your dataset. To speed up these queries you have to create indexes on type and flag fields.

select a.* from activities a
JOIN (select distinct type FROM activities where type='X' or flag=1) t
ON a.type=t.type;


select a.* from activities a
where type='X'
  or EXISTS(SELECT * FROM activities WHERE type=a.type AND flag=1);


select a.* from activities a
where type='X'
  or type IN (SELECT type FROM activities WHERE flag=1)

SQLFiddle demo

Upvotes: 1

Kirill Kin
Kirill Kin

Reputation: 391

Try this solution

SELECT id,
       type,
       value,
       flag
FROM (SELECT *,
      SUM(CASE WHEN flag = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY type) AS flag_occurence_for_type
      FROM activities) t
WHERE type = 'X' OR flag_occurence_for_type > 0

Upvotes: 1

Related Questions