Search code examples
sqloracleoracle10gtoad

Oracle query: how do I limit the returned records to only those having a count > 1 but show full results?

I need to show all the users who have more than one ID but not return the users who do. I tried group by having but I need to list the IDs and not just count them so could not get that to work for me. I ended up with using a the code below but it returns all the records.

select id,fname,lname,ssn,dob count(id) over partition by fname,lname,ssn,dob) as cnt from TABLE order by cnt desc;

Solution

  • Use a subquery:

    select id, fname, lname, ssn, dob
from (select id, fname, lname, ssn, dob,
             count(id) over (partition by fname, lname, ssn, dob) as cnt
      from TABLE
     ) t
where cnt >= 2
order by cnt;