How to group by fields which have specific value in SQL?

I have the following columns in my table (I'm using postgresql): parent, child, status. Each parent has 1 or 2 children.

I want to select all parents whose first child has status x and second child has status y or in case there's only 1 child than parent with child with status x. For example, a table with the following rows:

parent child status
a child1 x
a child2 y
b child3 x

should return parents a and b.

I started building the query something like:

select parent from "mytable"
group by parent
having ...

but I don't know how to add the above condition for status within having clause.

1 answer

  • answered 2021-06-23 11:58 Gordon Linoff

    SQL tables represent unordered sets. You need a column that specified what "first" and "second" means. Let me assume that is the child column.

    Then you can use:

    select parent
    from mytable
    group by parent
    having string_agg(status order by child) in ('x', 'xy');