INSERT INTO dup_values_tbl VALUES
(1,'SUDHARMA'
);
INSERT INTO dup_values_tbl VALUES
(2,'SHANKAR'
);
INSERT INTO dup_values_tbl VALUES
(3,'VIJAY'
);
INSERT INTO dup_values_tbl VALUES
(4,'VINITH'
);
INSERT INTO dup_values_tbl VALUES
(5,'VISWANATH'
);
INSERT INTO dup_values_tbl VALUES
(1,'SUDHARMA'
);
INSERT INTO dup_values_tbl VALUES
(2,'SHANKAR'
);
INSERT INTO dup_values_tbl VALUES
(3,'VIJAY'
);
INSERT INTO dup_values_tbl VALUES
(4,'VINITH'
);
INSERT INTO dup_values_tbl VALUES
(5,'VISWANATH'
);
Query to select the data in the table
SELECT * FROM DUP_VALUES_TBL;
DUP_ID
DUP_NAME
1
SUDHARMA
2
SHANKAR
3
Vijay
4
VINITH
5
VISWANATH
1
SUDHARMA
2
SHANKAR
3
Vijay
4
VINITH
5
VISWANATH
1.query to select unique records using distinct keyword
SELECT DISTINCT * FROM DUP_VALUES_TBL;
or
SELECT DISTINCT DUP_ID,DUP_NAME FROM DUP_VALUES_TBL;
DUP_ID
DUP_NAME
4
VINITH
1
SUDHARMA
2
SHANKAR
5
VISWANATH
3
VIJAY
2.query to select unique records using group by
SELECT DUP_ID,DUP_NAME FROM DUP_VALUES_TBL GROUP BY DUP_ID,DUP_NAME;
DUP_ID
DUP_NAME
4
VINITH
1
SUDHARMA
2
SHANKAR
5
VISWANATH
3
VIJAY
3.query to select unique records using unique keyword
SELECT UNIQUE DUP_ID,DUP_NAME FROM DUP_VALUES_TBL;
DUP_ID
DUP_NAME
4
VINITH
1
SUDHARMA
2
SHANKAR
5
VISWANATH
3
VIJAY
4.query to select unique records using union operator
SELECT DUP_ID,DUP_NAME FROM DUP_VALUES_TBL
UNION
SELECT DUP_ID,DUP_NAME FROM DUP_VALUES_TBL;
5.query to select unique records using minus operator
SELECT DUP_ID,DUP_NAME FROM DUP_VALUES_TBL
MINUS
SELECT NULL,NULL FROM DUP_VALUES_TBL;
6.query to select unique records using intersect operator
SELECT DUP_ID,DUP_NAME FROM DUP_VALUES_TBL
INTERSECT
SELECT DUP_ID,DUP_NAME FROM DUP_VALUES_TBL;
DUP_ID
DUP_NAME
1
SUDHARMA
2
SHANKAR
3
VIJAY
4
VINITH
5
VISWANATH
Note:
Set operators like Union, Minus, Intersect Performs sorting operation by default in ascending order.
The above result is same for Query 5 to 7.
7.query to select unique records using row_number() function
SELECT DUP_ID,
DUP_NAME
FROM
(SELECT DUP_ID,
DUP_NAME,
row_number() over (partition BY DUP_ID,DUP_NAME order by DUP_ID DESC) ROW_NUMBER
FROM DUP_VALUES_TBL
)
WHERE ROW_NUMBER=1 ;
8.query to select unique records using rank() function
SELECT DUP_ID,
DUP_NAME
FROM
(SELECT DUP_ID,
DUP_NAME,
RANK() over (partition BY DUP_ID,DUP_NAME order by ROWNUM DESC) ROW_NUMBER
FROM DUP_VALUES_TBL
)
WHERE ROW_NUMBER=1 ;
8.query to select unique records using dense_rank() function
SELECT DUP_ID,
DUP_NAME
FROM
(SELECT DUP_ID,
DUP_NAME,
DENSE_RANK() over (partition BY DUP_ID,DUP_NAME order by ROWNUM DESC) ROW_NUMBER
FROM DUP_VALUES_TBL
)
WHERE ROW_NUMBER=1 ;
9.query to select unique records using sub query
SELECT DUP_ID,
DUP_NAME
FROM DUP_VALUES_TBL a
WHERE a.ROWID >= ALL
(SELECT b.ROWID
FROM DUP_VALUES_TBL b
WHERE a.DUP_ID = b.DUP_ID
AND a.DUP_NAME = b.DUP_NAME
)
ORDER BY DUP_ID ,
DUP_NAME ;
10.query to select unique records using Max function
SELECT DUP_ID,
DUP_NAME
FROM DUP_VALUES_TBL a
WHERE a.ROWID = ALL
(SELECT MAX(b.ROWID)
FROM DUP_VALUES_TBL b
WHERE a.DUP_ID = b.DUP_ID
AND a.DUP_NAME = b.DUP_NAME
)
ORDER BY DUP_ID ,
DUP_NAME ;
10.query to select unique records using Min function
SELECT DUP_ID,
DUP_NAME
FROM DUP_VALUES_TBL a
WHERE a.ROWID = ALL
(SELECT MIN(b.ROWID)
FROM DUP_VALUES_TBL b
WHERE a.DUP_ID = b.DUP_ID
AND a.DUP_NAME = b.DUP_NAME
)
ORDER BY DUP_ID ,
DUP_NAME ;
11.query to select unique records using Not exists operator
SELECT DUP_ID,
DUP_NAME
FROM DUP_VALUES_TBL a
WHERE NOT EXISTS
(SELECT 1
FROM DUP_VALUES_TBL b
WHERE a.DUP_ID = b.DUP_ID
AND a.DUP_NAME = b.DUP_NAME
AND B.ROWID > A.ROWID
)
ORDER BY DUP_ID ,
DUP_NAME ;
12.query to select unique records using Not in operator
SELECT DUP_ID,
DUP_NAME
FROM DUP_VALUES_TBL a
WHERE A.ROWID NOT IN
(SELECT MIN(B.ROWID)
FROM DUP_VALUES_TBL b
WHERE a.DUP_ID = b.DUP_ID
AND a.DUP_NAME = b.DUP_NAME
AND B.ROWID > A.ROWID
)
ORDER BY DUP_ID ,
DUP_NAME ;