10 ways get Distinct/Unique record without using DISTINCT Oracle

Table Of Contents

How to get Distinct/Unique record without using DISTINCT SQL/Oracle?

  • Create table using following script:
CREATE TABLE TEST_EMP
(
  ENAME   VARCHAR2(10 BYTE),
  DEPTNO  NUMBER(2),
  ID      NUMBER
)
  • Run following script for demo purpose.
SET DEFINE OFF;
Insert into TEST_EMP (ENAME, DEPTNO, ID)
 Values ('SMITH', 20, 1);
Insert into TEST_EMP (ENAME, DEPTNO, ID)
 Values ('ALLEN', 30, 2);
Insert into TEST_EMP (ENAME, DEPTNO, ID)
 Values ('SMITH', 20, 3);
Insert into TEST_EMP (ENAME, DEPTNO, ID)
 Values   ('VIKAS', 10, 4);
Insert into TEST_EMP   (ENAME, DEPTNO, ID)
 Values   ('VIKAS', 10, 6);
Insert into TEST_EMP   (ENAME, DEPTNO, ID)
 Values   ('ASHISH', 40, 7);
Insert into TEST_EMP   (ENAME, DEPTNO, ID)
 Values   ('SATISH', 50, 8);
Insert into TEST_EMP   (ENAME, DEPTNO, ID)
 Values   ('SATISH', 50, 9);
COMMIT;
SELECT ENAME,DEPTNO
FROM TEST_EMP
ORDER BY ENAME,DEPTNO;

Preview

Solution 1 :

SELECT UNIQUE ENAME ,DEPTNO
FROM TEST_EMP
ORDER BY ENAME,DEPTNO;

Solution 2 :

SELECT ENAME ,DEPTNO
FROM TEST_EMP
GROUP BY ENAME,DEPTNO
ORDER BY ENAME,DEPTNO;

Solution 3 :

SELECT ENAME ,DEPTNO
FROM TEST_EMP
UNION
SELECT ENAME ,DEPTNO
FROM TEST_EMP
ORDER BY ENAME,DEPTNO;

Solution 4 :

SELECT ENAME ,DEPTNO
FROM TEST_EMP
UNION
SELECT NULL,NULL
FROM DUAL WHERE 1=2;

Solution 5 :

SELECT ENAME ,DEPTNO
FROM TEST_EMP
INTERSECT
SELECT ENAME ,DEPTNO
FROM TEST_EMP
ORDER BY ENAME,DEPTNO;

Solution 6 :

SELECT ENAME ,DEPTNO
FROM TEST_EMP
MINUS
SELECT NULL,NULL
FROM DUAL;

Solution 7 :

SELECT ENAME,DEPTNO FROM (
SELECT ENAME,DEPTNO,
    ROW_NUMBER() over(partition by ENAME,DEPTNO  ORDER BY ENAME,DEPTNO) R
FROM TEST_EMP)
where R=1;

Solution 8 :

SELECT ENAME,DEPTNO FROM (

SELECT ENAME,DEPTNO,
    RANK() over(partition by ENAME,DEPTNO  ORDER BY ROWNUM) R
FROM TEST_EMP
)
where R=1;

Solution 9 :

SELECT ENAME,DEPTNO FROM TEST_EMP
WHERE ROWID IN (
SELECT MIN(ROWID)
FROM TEST_EMP
GROUP BY ENAME,DEPTNO)

Solution 10 :

SELECT ENAME,DEPTNO  FROM TEST_EMP A
WHERE 1 = ( SELECT COUNT(1) 
            FROM TEST_EMP B 
            WHERE A.DEPTNO = B.DEPTNO
            AND A.ROWID >= B.ROWID);

comments powered by Disqus