4

I have a following table structure in Postgres:

 id | account_id | plan_id | active
----+------------+---------+--------
  1 |    0cYd7Ak |       1 |      f
  2 |    Uk02q1d |       1 |      t
  3 |    eRlk810 |       2 |      f
  4 |    Uk02q1d |       2 |      t
  5 |    0cYd7Ak |       1 |      t
  6 |    yT3nv3p |       3 |      t  

How I can select all row where account_id occurs more than once? For the example above it will return rows 1, 2, 4, 5

I tried this one query, but it does not return what I expect:

select * from table_name t1 
where (select count(*) from table_name t2 
  where t1.account_id = t2.account_id) > 1 
order by t1.account_id;
3
  • 5
    There are many ways to write this but your query seems correct. What results do you get? Jul 19, 2018 at 7:52
  • 1
    Ooops. You are right! It works. Unfortunately I tested this query yesterday night and probably was to tired to understood that it returns exactly what I need. Jul 19, 2018 at 9:58
  • Similar question: stackoverflow.com/q/9736944/6064933
    – jdhao
    Jun 3, 2022 at 14:20

2 Answers 2

8

Your query seems correct (although it may not be the most efficient).

There are many ways to write this type of query. For example, you could use EXISTS to avoid counting in the correlated subquery:

select * from table_name t1 
where exists 
      (select 1 from table_name t2 
       where t1.account_id = t2.account_id and t1.id <> t2.id) 
 ;

Another method is to use a subquery or CTE and window aggregate:

select id, account_id, plan_id, active
from 
  ( select *, 
           count(1) over (partition by account_id) as occurs
    from table_name
  ) AS t 
where occurs > 1 ;

Or use a subquery to find the accounts that have more than one occurrences and then join to the table:

select t.*
from 
  ( select account_id
    from table_name
    group by account_id
    -- having count(1) > 1          
    having min(id) <> max(id)      -- variation with same result 
  ) as c
  join table_name as t 
  on t.account_id = c.account_id ;
7

You can also try with HAVING clause.

select account_id, count(id) as repetita
from t1
group by account_id

having count(id) > 1 ;

with this solution remember to add the field you want to output to group by also

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .