Condition filtering SQL-CodePudding

I have a table Table name - commands

id	name	status	group_id
id - number	name - string	status - 0 or 1	group_id - number

I need to sort as follows: for all elements with the same group_id I have to check if at least one has a status of 1, if so, then leave, if not, then remove such a group and so on for all group_id

I tried to do it through GROUP BY, and then using HAVING to remove unnecessary groups, but this way I don't get the whole table to be displayed or a query that does not work.

I think it should look like:

SELECT COUNT(*) FROM commands GROUP BY group_id HAVING *condition*

Please let me know if there are any other commands to use.

id	name	status	group_id
1	name1	0	1
2	name2	0	1
3	name3	0	2
4	name4	1	2
5	name5	1	2
6	name6	0	3
7	name7	1	4

Result:

id	name	status	group_id
3	name3	0	2
4	name4	1	2
5	name5	1	2
7	name7	1	4

CodePudding user response：

In Postgres, that's a good spot to use a boolean window function:

select *
from (
    select t.*, bool_or(status = 1) over(partition by group_id) has_status_1
    from mytable t
) t
where has_status_1

bool_or checks if any row in the group satisfies its predicate ; we can use this information for filtering.

The upside is that the table is scanned only once, as opposed to the correlated subquery solution.

CodePudding user response：

You may use EXISTS operator with a correlated subquery as the following:

SELECT id, name, status, group_id
FROM table_name T
WHERE EXISTS(SELECT 1 FROM table_name D WHERE D.group_id = T.group_id AND D.status=1)
ORDER BY id

See a demo.