Create a column based on conditions in select statement

I have an order table in Postgres with order number and status as column. If a new order is placed the default status were Order placed and Goods Assigned.

order 12345

date        order  status
2020-06-01 12345 Goods Assigned
2020-06-01 12345 Order placed

order 543223

date        order  status
2020-05-02 54321   Reached pickup hub
2020-05-01 54321  Goods Assigned
2020-05-01 54321  Order placed

I want to know if the order were out from the sender place. So I want to create a column called started and has value Yes if we have any status after ‘Order placed’ and ‘Goods Assigned’. Else No if have only those two condition as below in Select statement

order Started
12345   No
54321  Yes

As ‘Order placed’ and ‘Goods Assigned’ was available in all the order I am unable to use NOT IN operator. Is there any other way we can get a report.

Answer

You can use aggregation:

select order_id, bool_or(status not in ('Goods Assigned', 'Order placed')) is_started
from mytable
group by order_id

For each order_id, this produces a boolean flag that indicates whether any ot its statuses has a value other than 'Goods Assigned' and 'Order placed'.