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.

Answer

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');