How Do I Select Duplicate Records from a Database Table

This T-SQL snippet allows you to select duplicate records from a database table. The example assumes that the column Id is used to determine whether a record has a duplicate. If multiple columns determine whether a record has a duplicate or not, simply expand the SELECT list and the GROUP BY clause.
SELECT
    Id
FROM
    aTable
GROUP BY
    Id
HAVING
    (COUNT(Id) > 1)

Where to Next?

Wonder where to go next? You can read existing comments below or you can post a comment yourself on this article .


Consider making a donation
Please consider making a donation using PayPal. Your donation helps me to pay the bills so I can keep running Imar.Spaanjaars.Com, providing fresh content as often as possible.



Feedback by Other Visitors of Imar.Spaanjaars.Com

On Sunday, November 14, 2004 10:10:03 PM Ray said:
Thanks it helped!
On Tuesday, February 28, 2006 8:53:34 PM Richard Stelma said:
I like this simple select approach. I've used it quite often.

I found this link which provides a safe, flexible way of dealing with duplicates. Dealing with duplicates can get very complicated especially when you want to delete them.

http://www.sql-server-performance.com/rd_delete_duplicates.asp
On Tuesday, February 28, 2006 9:08:35 PM Imar Spaanjaars said:
Hi Richard,

Interesting article; thanks for the link.

Imar
On Monday, September 18, 2006 12:26:18 PM Lalit Pradhan said:
ExecuteNonQuery() method not returning correct value.

Hi Imar,
Sorry for writing this problem on your site.But you are the last hope for the solution of this problem as I didn't got the response from P2P wrox forum.I have presented it to many other professionals also but they are unable to tell the reason why ExecuteNonQuery() method not giving the desired output to me.
Iam facing a very strange problem regarding ExecuteNonQuery() function in my C# class library for my project.My page(ChangePassword.aspx) changes the existing user's password when the password for the existing user is entered correctly.For that I have developed an application(or just a module) based on 3 tier architecture.

On the Data layer (i.e. the database side) I wrote a stored procedure called spUserUpd which has the following code:

CREATE PROCEDURE [dbo].[spUserUpd]
(    
     @option int = NULL,
     @userName varchar(100) = NULL,
     @userPassword varchar(50) = NULL,
     @newPassword varchar(50) = NULL    
)
AS

SET NOCOUNT ON

IF (@option = 3)
BEGIN
    IF EXISTS(SELECT UserName FROM dbo.tbUsers
                       WHERE UserName =@userName AND UserPassword = dbo.fnMakeSHA1(@userPassword) AND Status = 'A')
        
        BEGIN
            UPDATE  dbo.tbUsers
                                SET UserPassword =  dbo.fnMakeSHA1(@newPassword)
                                                     , UpdatedDate = GETDATE()
                                WHERE UserName = @userName  AND Status = 'A'
        END
    
    ELSE
        BEGIN
            SELECT 0 AS RowsAffected
        END                                                  
END
GO

Now Iam accessing this stored procedure through my class library(CCR.dll) which is my bussiness layer which has the following code:

public int ChangeUserPassword(string loginName, string oldPassword, string newPassword)
        {
            int rowsAffected = 0;
            try
            {
                Con = new SqlConnection(Connection.ConnectionString);
                Con.Open();
                using (Con)
                {
                    SqlCommand cmd = new SqlCommand("[dbo].[spUserUpd]", Con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@option", SqlDbType.Int).Value = 3;
                    cmd.Parameters.Add("@userName", SqlDbType.VarChar).Value = loginName;
                    cmd.Parameters.Add("@userPassword", SqlDbType.VarChar).Value = oldPassword;
                    cmd.Parameters.Add("@newPassword", SqlDbType.VarChar).Value = newPassword;

                    rowsAffected = cmd.ExecuteNonQuery();
                    return rowsAffected;
                }
            }
            catch (Exception ex)
            {
                return 0;
            }
        }

And iam calling this funtion of the dll through my ChangePassword.aspx.cs which is the presentation layer of my project.The code for calling it as follows:

protected void ibtnChangePassword_Click(object sender, ImageClickEventArgs e)
    {
        string loginName;
        string oldPassword;
        string newPassword;
        int IsChanged;
        
        User objUser = new User();

        loginName = txtLoginName.Text;
        oldPassword = txtOldPassword.Text;
        newPassword = txtNewPassword.Text;

IsChanged = objUser.ChangeUserPassword(loginName, oldPassword, newPassword);

        if (IsChanged == 1)
        {
            lblMessage.Text = "Password changed successfully";
        }
        else
        {
            lblMessage.Text = "Password change operation failed";
        }
}

Now the thing is my application is working fine and capable of changing the existing user password if the old password entered is correct.But Iam facing the problem in displaying the message if the password has been successfully changed.I always get the message
"Password change operation failed"; irrespective of weather password has been changed successfully or it has failed to changed.
And I figured out the reason too.Its happening bcoz the given line on my dll
              int rowsAffected = 0;
              rowsAffected = cmd.ExecuteNonQuery();
                    return rowsAffected;

is not giving me the desired output.Iam always getting the value of rowsAffected = -1 which is not supposed to be. It should return a value of 1 as we all know ExecuteNonQuery() returns the number of rows affected when it applies to UPDATE, INSERT or DELETE statements.And in my case only 1 rows get affected. For all other statements it returns the value of -1.
My question is why here ExecuteNonQuery()is not working properly as it should be.
I HAD GONE THROUGH THE MSDN HELP ON THE FOLLOWING LINK
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlCommandClassExecuteNonQueryTopic.asp

but it doesn't help me out. I had also gone to wrox press's Begining VC# 2005 and got the example on chapter 24, page no. 819 under Retrieving No Data section and Try it out named Data Modification with ExecuteNonQuery which shows that it returns the number of rows affected when it is used for UPDATE statement.Please go through the example if it is possible for all of you.
All I want to know why ExecuteNonQuery() is not giving the intended results to me?
Does it mean that ExecuteNonQuery() is not supposed to work with 3-tier architectures?
                          ExecuteNonQuery() method not giving proper result
Its not just a question of displaying a message after changing password but now its also a question of correct working of ExecuteNonQuery()function of ADO.NET.
Please help me out. Iam very curious to know the answers of my queries.Hoping for early response from experienced developers as well as all who are interested in telling me the solution of this problem.

Thanks a lot for paying attention to this long mail and reading it.    

Lalit
On Monday, September 18, 2006 12:34:53 PM Imar Spaanjaars said:
Hi Lalit,

Can you please not use the Talk Back feature as your personal private forum? This isn't really related to the original topic of the article...

If you post the link to the Wrox post, I'll take a look there.... Can you also add your table structure to that post if you haven't already done so?

Imar
On Monday, March 26, 2007 7:17:01 AM Vignesh said:
Hi Imar,
  Nice post. If by migration or something we got Primary key column duplicated in our DB. In this case should I be grouping all the columns in the table to the above query.
Thanks
On Monday, March 26, 2007 7:39:26 PM Imar Spaanjaars said:
Hi Vignesh,

Sorry, I don't understand what you're asking. Can you elaborate a little?

Imar
On Wednesday, May 23, 2012 11:29:57 AM sandip narayan rakshak said:
it's very good for every one
On Tuesday, December 18, 2012 4:53:47 AM javame said:
It get more tricky when you have more than one column which defines uniqueness, its important to include both of them in group by clause as shown in http://javarevisited.blogspot.kr/2012/12/how-to-find-duplicate-records-in-table-mysql-query-example.html", leaving any one of them will result in incorrect result set.

Talk Back! Comment on Imar.Spaanjaars.Com

I am interested in what you have to say about this article. Feel free to post any comments, remarks or questions you may have about this article. The Talk Back feature is not meant for technical questions that are not directly related to this article. So, a post like "Hey, can you tell me how I can upload files to a MySQL database in PHP?" is likely to be removed. Also spam and unrealistic job offers will be deleted immediately.

When you post a comment, you have to provide your name and the comment. Your e-mail address is optional and you only need to provide it if you want me to contact you. It will not be displayed along with your comment. I got sick and tired of the comment spam I was receiving, so I have protected this page with a simple calculation exercise. This means that if you want to leave a comment, you'll need to complete the calculation before you hit the Post Comment button.

If you want to object to a comment made by another visitor, be sure to contact me and I'll look into it ASAP. Don't forget to mention the page link, or the QuickDocId of the document.

For more information about the Talk Back feature, check out this news item.