Question 113788 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Condition filtering SQL

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
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search