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

PostgreSQL: skip selecting row if found more than one row with similar column value

I have the following selection:

SELECT
    posm.id AS org_scope_metric_id,
    cc.code AS control_code,
    ccp.code AS part_code,
    pop.id AS program_id
FROM public.org_scope_metric posm
JOIN public.org_metric pom ON pom.id = posm.metric_id
JOIN compliance.metric cm ON cm.id = pom.metric_id
JOIN compliance.control_part_metric ccpm ON ccpm.metric_id = cm.id
JOIN compliance.control_part ccp ON ccp.id = ccpm.part_id
JOIN compliance.control cc ON cc.id = ccp.control_id
JOIN compliance.program_framework cpf ON cpf.framework_id = cc.framework_id
JOIN public.org_program pop ON pop.program_id = cpf.program_id
WHERE posm.scope_id ='e6d11bfe-e5fc-11ee-8cda-238cdb93d281'
GROUP BY posm.id, cc.code, ccp.code, pop.id

that returns such result:
enter image description here

If there are org_scope_metric_ids with different program_id I need to skip them all from the selection.

Please help to resolve it!

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you so much guys. My final solution according to your suggestions is

    SELECT
   org_scope_metric_id,
   program_scope_id,
   array_agg(program_scope_id) OVER (PARTITION BY org_scope_metric_id) AS metric_program_scopes
FROM (
  <long_selection_from_the_question>
) subquery
GROUP BY subquery.org_scope_metric_id, subquery.program_scope_id

    So the main thing that I had to use OVER (PARTITION BY) to get necessary count.


  2. 0

    Count the number of equal org_scope_metric_id occurrences and the number of distinct program_id-s in each group and then filter result rows with both numbers greater than 1.

    with t as ( 
  select *, 
    count(*) over w cnt,
    array_agg(program_id) over w program_id_list
  from (<your query here>) t 
  window w as (partition by org_scope_metric_id)
)
select * from t 
where cnt > 1 and array_distinct_count(program_id_list) > 1;

    You would need a helper function for that –

    create or replace function array_distinct_count(arr anyarray)
returns integer language sql immutable as $$
 select count(distinct el) from unnest(arr) el;
$$;
    Login or Signup to reply.
  3. 0

    You can use min and max window functions

    SELECT *
FROM (
    SELECT
        posm.id AS org_scope_metric_id,
        cc.code AS control_code,
        ccp.code AS part_code,
        pop.id AS program_id,
        MIN(pop.id) OVER (PARTITION BY posm.id) = MAX(pop.id) OVER (PARTITION BY posm.id) AS all_same_id
    FROM public.org_scope_metric posm
    JOIN public.org_metric pom ON pom.id = posm.metric_id
    JOIN compliance.metric cm ON cm.id = pom.metric_id
    JOIN compliance.control_part_metric ccpm ON ccpm.metric_id = cm.id
    JOIN compliance.control_part ccp ON ccp.id = ccpm.part_id
    JOIN compliance.control cc ON cc.id = ccp.control_id
    JOIN compliance.program_framework cpf ON cpf.framework_id = cc.framework_id
    JOIN public.org_program pop ON pop.program_id = cpf.program_id
    WHERE posm.scope_id ='e6d11bfe-e5fc-11ee-8cda-238cdb93d281'
    GROUP BY posm.id, cc.code, ccp.code, pop.id
) p
WHERE all_same_id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search