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

PostgreSQL: Select 2 or more records with the same id given a column value

I have a table that has a column called id. The table name is called names.
I need to show all the id’s that have one or more records on the table, and the corresponding fields on the table.
The search criteria is WHERE org = ‘A’. If I search just for org = ‘A’ then I will show only those records. I need to be able to also show the other records with the same id.

I tried

SELECT id, name, state, org
FROM names
WHERE 
HAVING count(org) > 2
GROUP BY id, name, state, org

names table

id name state org
111 John GA A
111 April FL B
112 Max GA A
113 Alex NC C
114 Steve SC A
114 Rachel AL B
114 Brad TN D

Desired output

id name state org
111 John GA A
111 April FL B
114 Steve SC A
114 Rachel AL B
114 Brad TN D

Thanks

Tags:

4

Answers


  1. 0

    Your query could be:

    with tmp as (
  select names.*,
        count(*) over (partition by id) as same_id_count
  from names
)
select id, name, state, org 
from tmp
where same_id_count > 1 and id in (select distinct id 
                                   from names 
                                   where org = 'A');

    Details:

    • tmp CTE query helps calculate the number of rows with the same id,
      with the help of count() window function;
    • in the outer query you filter the rows by 'A' org-filter and the same_id_count > 1 filter.

    DB fiddle

    Login or Signup to reply.
  2. 0

    Extract the ids with more than one record and then use them to query

    SELECT * from names where id in (SELECT id from names GROUP BY id having COUNT(id)>1 );
    Login or Signup to reply.
  3. 0

    You need to check the existence of org = A records and then fetch the matching id records. Then filter the records having more than 1 count –

    SELECT id, name, state, org
  FROM (SELECT *, COUNT(*) OVER(PARTITION BY id) cnt
          FROM names n1
         WHERE EXISTS(SELECT NULL
                        FROM names n2
                       WHERE n1.id = n2.id
                         AND n2.org = 'A'
                     )
       ) x
 WHERE cnt > 1;

    Demo.

    Login or Signup to reply.
  4. 0

    To be able to select 2 or more records with the same id given a column value we can use the following query

    SELECT id, name, state, org
FROM names
WHERE id IN (
    SELECT id
    FROM names
    GROUP BY id
    HAVING COUNT(id) >= 2
);

    The following query will first select all the id’s that have one or more records on the table, and the corresponding fields on the table. Then, it will use the IN operator to filter the results to only include rows with those id’s.

    This will give the same output as the desired output displayed in your question.

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

Back To Top
Search