CODEWITHSUNDEEP
sqlpostgresqlgroup-by
Phill Pafford
Phill Pafford

Reputation: 85298

results for each group

UPDATED:

There can be is_active = true after is_active = false, so I need to get the last assigned is_active = true and get the preceding is_active = false record

I have a table with this structure

id (not pk but each group is incremental), name, grp, is_active (boolean)

with data like this for group 1

1, name1, group1, true
2, name2, group1, true
3, name3, group1, false
4, name1, group1, false
5, name2, group1, true
6, name3, group1, false <-- this is the next assigned id as the preceding record has an is_active = true
7, (names will differt, group the same and all false)...

... more data in the same table but for group2

100, name1, group1, true
101, name2, group1, true
102, name3, group1, true
103, name1, group1, true
104, name2, group1, true
105, name3, group1, false <-- this is the next assigned id as the preceding record has an is_active = true
106, (names will differt, group the same and all false)...

I have this but it's not working as I would like it to

SELECT grp, COUNT(*)
FROM tbl_1
WHERE is_active = false
AND id > (
    SELECT id
    FROM tbl_1
    WHERE is_active = true
    ORDER BY id DESC
    LIMIT 1
)
GROUP BY grp

So the return I would like is:

group1, 6
group2, 105

but I get only

group2, 105

Upvotes: 2

Views: 96

Answers (3)

dee-see
dee-see

Reputation: 24078

You only really need to find the first id where is_active = false. It is not necessary to apply "greater than first active" condition on id.

SELECT MIN(id), grp
  FROM tbl_1
  WHERE is_active = false
  GROUP BY grp

UPDATE:

Answer to the updated question:

SELECT min(id), grp
  FROM tbl_1 t
  WHERE is_active = false
  AND id > (SELECT max(id) 
              FROM tbl_1 
              WHERE is_active = true 
                AND grp = t.grp)
  GROUP BY grp;

Upvotes: 3

Michael Riley - AKA Gunny
Michael Riley - AKA Gunny

Reputation: 5184

SELECT 
grp,
MIN(Id)
FROM tbl_1
WHERE is_active = false 
GROUP BY grp

Upvotes: 2

JK.
JK.

Reputation: 5136

SELECT grp, MIN(id) FROM tbl_1 WHERE is_active = false GROUP BY grp;

Upvotes: 2

Related Questions