Oracle select same id where 2 exist

so this query of this Oracle table shows 2 rows with same id

SELECT INDIVIDUAL_ID,DATE1,DATE2
FROM RESIDENCE_PERIOD
WHERE INDIVIDUAL_ID = 401865003800;

all good —

401865003800    20-05-02    20-05-20
401865003800    20-06-01    20-06-30

but there are other ids that have more than 1 entry as well so this query:

  SELECT * 
  FROM RESIDENCE_PERIOD rp 
  WHERE  rp.INDIVIDUAL_ID IN
                    (SELECT rp2.INDIVIDUAL_ID
                     FROM RESIDENCE_PERIOD rp2
                    GROUP BY rp2.INDIVIDUAL_ID
                    HAVING COUNT(rp2.INDIVIDUAL_ID) > 1)
    AND ROWNUM <=2
  ORDER BY rp.INDIVIDUAL_ID;

works but brings back 2 different ids that both have more than 1 entry

276460366513
401865003800

I don’t care which id I get. I just want both of the ids to be the same.

Answer

You better use an analytical function like this to fetch the entire row for all the same ids having more than one record:

  Select p.* from
     (Select p.*,
             Count(1) over (partition by P.INDIVIDUAL_ID ) as cnt
        From RESIDENCE_PERIOD p) p
       Where cnt>1
 Order by INDIVIDUAL_ID