Recent Posts

Thursday, August 7, 2008

How to find duplicate records in a Table (SQL Query)

Books table might have multiple bookKey. So to find out duplicate rows in a table which has thousands of rows can be tricky.

So the following query gets the duplicate records from the table and displays it and if you just want the count then you can use only the internal select statement only.

select b.* from (select bookkey, count(bookkey)as dup from Books where bookkey>0 group by bookkey) a,
books b where b.bookkey = a.bookkey and a.dup >1 order by b.bookkey

select b.* from (select bookkey from books group by bookkey having count(bookkey)>1) a,
books b where b.bookkey = a.bookkey order by b.bookkey

Related Posts by Categories




No comments:

Post a Comment