By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table A (
id int,
name varchar(255),
status int
);
insert into A values (11, 'name1' , 3 );
insert into A values (21, 'name1' , 0 );
insert into A values (9, 'name1' , 0 );
insert into A values (33, 'name2' , 0 );
insert into A values (22, 'name2' , 0 );
insert into A values (44, 'name2' , 0 );
SELECT (CASE
WHEN max(status) = 0 THEN max(id)
WHEN max(status) = 3 THEN
(SELECT id
FROM A a2
WHERE status = 3
AND a1.name = a2.name)
END) AS aggregate_id,
name
FROM A a1
GROUP BY name
| aggregate_id | name |
|---|---|
| 11 | name1 |
| 44 | name2 |