Sql Duplicates

How to find duplicate using sql duplicates? If you have a table and you are wondering if there is any duplicate record in it or not, what do you do?. There are two possible situations:

  • one. there is a primary key:

    If the primary key is rowID, the following SQL will show all duplicates row:

    SELECT rowid
    FROM   tbl
    GROUP  BY rowid
    HAVING COUNT(*) > 1; 

    The duplicates shown here will only have one colum,i.e. the primary key column. What if you are interested to see the rest of the columns? To do this you need to use Sql Subquery. This is how you do it: SELECT *
    FROM   tbl
    WHERE  rowid IN (SELECT rowid
                     FROM   tbl
                     GROUP  BY rowid
                     HAVING COUNT(*) > 1); 

  • two. there is no primary key:

    This is a situation when the same records added several times to a table.

    To show all duplicate rows, as the whole, use the following Sql(for sake of simplicity, let's assume the table has got only 3 columns):

    SELECT firstname,
           lastname,
           address
    FROM   tbl
    GROUP  BY firstname,
              lastname,
              address
    HAVING COUNT(*) > 1.; 

Only duplicate records will be shown in the result. That leads to a conclusion that if there is nothing in the result that means there is no duplicate record in the table(but you may have to be careful on a certain situation,I'll discuss this later).

There are some other ways to do the same thing (I believe this applies to almost anything), such as using sql subquery or sql join. I'll discuss this too sometime later.

Here's for you to think. You know there's duplicate in a table, but how do you remove the duplicate records?.

I shall discuss that next.

Journey
Buy at AllPosters.com
A bend in the road is not the end of the road... unless you fail to make the turn

Positive Resources

All of us have at least two or three things that excite us. We could talk about these subjects for hours. Good news!. Did you know that Site Built It! can turn this type of deep interest and enthusiasm into a revenue-generating business?

Review some of the Site Build It! case studies and you'll absolutely discover something that ignites more positive ideas into your mind.

SBI! Video Tour


Invest the most
important 30 minutes
you will spend online.

Click, sit back, and
understand why SBI!
makes you succeed.