ROW_NUMBER To Trap Duplicate Data

The Oracle analytic function ROW_NUMBER() can be used identify duplicate data as shown in the query below. It is easy to analyze and/or delete duplicate if a unique record identifier exists in the table. In the absence of a unique record identifier, ROWID can be used (refer - Delete Duplicates ).

ROW_NUMBER()

SELECT  q_dup.idx, q_dup.row_num,
 DECODE(q_dup.row_num,
    1,NULL,'Duplicate') data_desc
FROM
(SELECT q_tab.idx,
  ROW_NUMBER() OVER(PARTITION BY q_tab.idx
        ORDER BY q_tab.idx) row_num
 FROM 
   (SELECT 1 idx FROM DUAL UNION ALL
    SELECT 1 idx FROM DUAL UNION ALL
    SELECT 1 idx FROM DUAL UNION ALL
    SELECT 2 idx FROM DUAL UNION ALL
    SELECT 3 idx FROM DUAL UNION ALL
    SELECT 3 idx FROM DUAL UNION ALL
    SELECT 4 idx FROM DUAL) q_tab
) q_dup;

-- Query to select unique data
SELECT q_dup.idx  unique_val FROM
(SELECT q_tab.idx,
  ROW_NUMBER() OVER(PARTITION BY q_tab.idx
        ORDER BY q_tab.idx) row_num
 FROM (SELECT 1 idx FROM DUAL UNION ALL
    SELECT 1 idx FROM DUAL UNION ALL
    SELECT 1 idx FROM DUAL UNION ALL
    SELECT 2 idx FROM DUAL UNION ALL
    SELECT 3 idx FROM DUAL UNION ALL
    SELECT 3 idx FROM DUAL UNION ALL
    SELECT 4 idx FROM DUAL) q_tab
) q_dup
WHERE  q_dup.row_num = 1;

-- Above query Output

Oracle registered trademark of Oracle Corporation.

Last Revised on: March 01, 2012

74121