10 ways get Distinct/Unique record without using DISTINCT Oracle

June 9, 2021Posted inORACLE, SQL

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

  • Create table using following script:
Copy Code
CREATE TABLE test_emp
(
   ename    VARCHAR2 (10 BYTE),
   deptno   NUMBER (2),
   id       NUMBER
);
  • Run following script for demo purpose.
Copy Code
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;

Solution 1 :

Copy Code
  SELECT UNIQUE ename, deptno
    FROM test_emp
ORDER BY ename, deptno;

Solution 2 :

Copy Code
  SELECT ename, deptno
    FROM test_emp
GROUP BY ename, deptno
ORDER BY ename, deptno;

Solution 3 :

Copy Code
SELECT ename, deptno FROM test_emp
UNION
SELECT ename, deptno FROM test_emp
ORDER BY ename, deptno;

Solution 4 :

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


Solution 5 :

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


Solution 6 :

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


Solution 7 :

Copy Code
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 :

Copy Code
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 :

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


Solution 10 :

Copy Code
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);
Tags: