I am trying to get two separate row values, for 2 conditions on the same column.
for example with this data:
id status
----------------
1 0
1 2
1 3
2 2
2 0
I want to select all the rows where the status = 0, and status = 2.
So for example the output should be:
id status
----------------
1 0
1 2
2 0
2 2
Thank you!
One method is:
where status in (0, 2)
But I suspect you want both values for the id. In that case, one method uses exists:
select t.*
from t
where status in (0, 2) and
exists (select 1
from t t2
where t2.id = t.id and
t2.status in (0, 2) and
t2.status <> t.status
);
If you just want the ids, then aggregation is easy:
select id
from t
where status in (0, 2)
group by id
having count(*) = 2;
This can be incorporated in a query to get the original rows using in, exists, or join. Or window functions:
select t.*
from (select t.*,
count(*) filter (where status in (0, 2)) over (partition by id) as cnt
from t
) t
where cnt = 2;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments