Friday, June 26, 2009

How to get Duplicate records in a Table

Last day, I seamlessly entered into a situation where I want to list out all the repeating/duplicate records from a table. Though it sounds simple, its bit a tricky approach.

As a small example: consider a table say RepeatTest having 2 fields: Id and Url. To figure out the repeating Url records, you can write the query as:

   2: SELECT Url, count(Url) FROM RepeatTest
   3:     GROUP BY Url
   4:         HAVING (COUNT(Url)>1)

Sometimes you may also jump into sudden simple queries, as what I’d jumped into.

Update: This simple question was asked for the Huwaei International's written exam, held at Bangalore on July 2009.

No comments:

Best viewed in Internet Explorer 8.