Duplicate Record Removes

----- Total number of duplicate records.

select A.vehregno,count(A.vehregno)
from scanlog A
where exists ( select *
               from  scanlog B
                where A.rowid <> B.rowid and
                       B.VEHREGNO = A.VEHREGNO and
                      B.SCANDATE = A.SCANDATE and
B.FILENAME=A.FILENAME )
group by A.VEHREGNO having count(A.VEHREGNO) > 1 order by count(A.VEHREGNO);



----- Total number of records after eliminating Duplicate record.

SELECT count(1)
     FROM scanlog A
    WHERE ROWID IN (SELECT rid
                      FROM (SELECT ROWID rid, ROW_NUMBER() OVER (PARTITION BY vehregno, scandate, filename ORDER BY ROWID) rn
                              FROM scanlog)
                     WHERE rn=1) order by vehregno;



----- Create Table After Eliminating the duplicate record.

CREATE  TABLE scanlog NOLOGGING
   AS
   SELECT A.*
     FROM scanlog A
    WHERE ROWID IN (SELECT rid
                      FROM (SELECT ROWID rid, ROW_NUMBER() OVER (PARTITION BY vehregno, scandate, filename ORDER BY ROWID) rn
                              FROM scanlog)
                     WHERE rn=1);



----- Find Duplicate Record.
 select *   from
    (select d.* , count(*) over(partition by binwardno) cnt
     from biolic d  )
    where cnt > 1;