0

Queries to find duplicate rows in a database and to delete them. Can anyone explain these queries how they are fetching the result ?

SELECT 
     * 
FROM 
     emp a 
WHERE 
     rowid = (SELECT MAX(rowid) 
              FROM EMP b 
              WHERE a.empno=b.empno)

to Delete: 

DELETE FROM 
     emp a 
WHERE 
     rowid != (SELECT MAX(rowid) 
               FROM emp b 
               WHERE a.empno=b.empno)
Matt Cremeens
  • 4,951
  • 7
  • 38
  • 67
Prabhat Gupta
  • 13
  • 3
  • You are asking the question or providing the solution I am confused. – Shushil Bohara Sep 09 '16 at 17:04
  • 1
    @Suraz they are asking to explain what is happening but Marc closed it thinking it was a dupe question of HOW to do it. OP found out how to do it, but doesn't understand how SQL is doing it / executing it it seems. – S3S Sep 09 '16 at 17:05
  • the first query SELECT ... simply shows you what the unique record will be if you want to keep the record that has the MAX(rowid).. The second actually deletes any record that is not that one. – Matt Sep 09 '16 at 17:14
  • @Prabhat Gupta in first select/from table emp is aliased to a . The selection is based on where clause : max(rowid) in other words looking for more than 1 rowid Duplicates From the emp table aliased as b Where (Table a.empno = Table b.empno ) .think of a and b as the same table sets only different instances. – Ken Sep 09 '16 at 19:08

0 Answers0