Find duplicate/repeated rows in hierarchical sql

Question

I'm attempting to detect duplicated/repeated values within a hierarchical table.

Consider the following (slightly contrived) example:

SELECT *
FROM   emp
START WITH mgr IN (SELECT empno FROM emp WHERE ename = 'JONES'
                   UNION ALL
                   SELECT empno FROM emp WHERE ename = 'JONES')
CONNECT BY PRIOR empno = mgr;

Returns...

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

What I actually want is...

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

ie I want each row to be returned as many times as it exists in the sub-query (ignoring the order). Since the START WITH is using an IN clause, the repeated values are being suppressed. Is it possible to reorganise the SQL so that I can do this?

Note that in my case the sub-clause is not a UNION, but is a SELECT which may return multiple (possibly duplicate) values from a table.

I could do it in PL/SQL by writing the values into a temp table and then GROUPing + COUNTing, but I'd prefer to do it in SQL only if possible.

Let me know if any clarification is required.

Thanks :-)

EDIT:

Note that there may be 0...N values returned from the sub-query.

Answer 1

Try This one..

SELECT  EMPNO,ENAME FROM,count(*)as counts   emp group by EMPNO,ENAME having count(*)>1