Postgres Subquery Limit based on Condition

Question

I have a table events that contains a type and a resourceId. I am looking to find resources that contain all of a certain event.type

The query I have so far is:

SELECT
    "resourceId",
    case
        WHEN array_agg(DISTINCT "type") @> '{0,1}'::INTEGER[] THEN 1
        ELSE 0
    END AS "containsEvents"
FROM
    events
GROUP BY
    "resourceId"
--  I WANT TO DO THIS vvv
-- HAVING "containsEvents" = 1
LIMIT 10;

If I'm not mistaken, MySQL allows HAVING in aliases. Is there a way around this in Postgres?

Gordon Linoff · Accepted Answer · 2020-10-08 22:14:49Z

1

Is this the logic you want?

SELECT "resourceId",
FROM events
GROUP BY "resourceId"
HAVING array_agg(DISTINCT "type") @> '{0,1}'::INTEGER[]
LIMIT 10;

answered Oct 8, 2020 at 22:14

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kyle V Over a year ago

It is indeed. I could have sworn I tried this, but I must've done the agg in WHERE and not HAVING. Thanks!

GMB · Accepted Answer · 2020-10-08 22:18:13Z

0

I understand that you want resources that have both types 0 and 1. I don' think you need array aggregation. I would phrase this as:

select "resourceId"
from events
where "type" in (0, 1)
group by "resourceId"
having count(*) = 2

This assumes no duplicate ("resourceId", "type"). If not, you can just change count(*) = 2 to count(distinct "type") = 2 in the having clause.

If you want resources that have both 0 and 1 types and no other type, then:

select "resourceId"
from events
group by "resourceId"
having 
    count(*) filter(where type in (1, 2)) = 2
    -- or count(distinct type) filter(where type in (1, 2)) = 2
    and count(*) filter(where type not in (1, 2)) = 0

edited Oct 8, 2020 at 22:18

answered Oct 8, 2020 at 22:14

GMB

224k25 gold badges103 silver badges151 bronze badges

Collectives™ on Stack Overflow

Postgres Subquery Limit based on Condition

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related