How to select UNIQUE records from a table using a SQL Query

Methods covered:

• ROW_NUMBER() with PARTITION BY
• GROUP BY

1. Create the sample table

Drop table if it already exists:

DROP TABLE T_UNIQUE;

Create table:

CREATE TABLE T_UNIQUE (
  ID   NUMBER(6,0),
  NAME VARCHAR2(20),
  SAL  NUMBER(10)
);

Insert sample data:

INSERT INTO T_UNIQUE(ID,NAME,SAL) VALUES(100,'RAVI',2100);
INSERT INTO T_UNIQUE(ID,NAME,SAL) VALUES(100,'RAVI',2100);
INSERT INTO T_UNIQUE(ID,NAME,SAL) VALUES(101,'SCOTT',3100);
INSERT INTO T_UNIQUE(ID,NAME,SAL) VALUES(101,'SCOTT',3100);
INSERT INTO T_UNIQUE(ID,NAME,SAL) VALUES(101,'SCOTT',3100);
INSERT INTO T_UNIQUE(ID,NAME,SAL) VALUES(102,'CLARK',4100);
INSERT INTO T_UNIQUE(ID,NAME,SAL) VALUES(102,'CLARK',4100);
INSERT INTO T_UNIQUE(ID,NAME,SAL) VALUES(103,'DAVIS',51000);

COMMIT;

View the table:

SELECT * FROM T_UNIQUE;

2. Using ROW_NUMBER()

The ROW_NUMBER() function assigns a number to each row within a partition. Here, duplicate rows are grouped by ID, NAME, and SAL. Then only the first row from each group is kept.

Step 1

SELECT ID, NAME, SAL,
       ROW_NUMBER() OVER (PARTITION BY ID, NAME, SAL ORDER BY ID) AS RN
FROM T_UNIQUE;

Step 2

SELECT ID, NAME, SAL
FROM (
    SELECT ID, NAME, SAL,
           ROW_NUMBER() OVER (PARTITION BY ID, NAME, SAL ORDER BY ID) AS RN
    FROM T_UNIQUE
)
WHERE RN = 1;

Result:

ID	NAME	SAL
100	RAVI	2100
101	SCOTT	3100
102	CLARK	4100
103	DAVIS	51000

3. Using GROUP BY

Another simple way to remove duplicates is by using GROUP BY. Since duplicate rows are identical, Oracle returns only one row for each unique combination.

SELECT ID, NAME, SAL
FROM T_UNIQUE
GROUP BY ID, NAME, SAL;

Result:

ID	NAME	SAL
101	SCOTT	3100
102	CLARK	4100
103	DAVIS	51000
100	RAVI	2100

Which method is better?

ROW_NUMBER() is more flexible when you want to keep a specific row from each duplicate set. GROUP BY is simpler when the duplicate rows are exactly the same and you only need one copy.

Key Point:
Use ROW_NUMBER() when you want full control over which duplicate row to keep. Use GROUP BY when you only need distinct rows from identical duplicates.