Journey to SQL Authority with Pinal Dave

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | Tagged Duplicate Records | 44 Comments

44 Responses

1. on August 14, 2007 at 5:28 am jaipal

   its a good solution so far …
   thanks

   
   
2. on August 28, 2007 at 1:55 am sdp

   Great solution, many thanks for this.

   
   
3. on September 3, 2007 at 11:26 pm Rana

   Dear Pinal,

   I visited your blog so manytimes and get the solution. Thank you so much for keep updating your blog.

   I have problem and i wanted you to give some time to resolve and given me a solution.

   I have one table containing the records like this

   Movie ID Movie Name New Name
   1 Spider Man Spider Man
   2 Spider Man 2 Spider Man
   3 Spider Man 3 Spider Man
   4 Spider Man UK Spider Man
   5 Spider Man USA Spider Man
   6 New Spider Man Spider Man
   7 Spider Man Black Spider Man
   8 Spider Man Part 1 Spider Man
   9 Spider Man Part 2 Spider Man
   10 Spider Man I Spider Man
   11 Spider Man III Spider Man
   12 Spider Man Part II Spider Man

   My manufacturer send me the data in this format and i have to allocate there new name to do some comparison

   I wanted to make this process automatic.
   what i mean is i need a query which will give me a duplicate records and also the suggestion for the new name.

   I am fully confident that you will help me out from this problem.

   Looking forward
   Rana

   
   
4. on September 5, 2007 at 5:12 am Rana

   Dear pinal,

   any luck with the solutions.

   
   
5. on September 5, 2007 at 7:35 am pinaldave

   Rana,

   Not yet, I many not have time to write custom solution in near future. I will look into it soon.

   Regards,
   Pinal

   
   
6. on September 7, 2007 at 5:01 am Rana

   Please find some time to write custom solution.

   I hope you understand my problem and how to resolve it.

   Good Luck.

   Looking forward on this weekend.

   Rana

   
   
7. on September 10, 2007 at 5:18 pm Dominator

   Dude rana, why dont you find your own solution instead of scamming people who actually know how to write sql.

   
   
8. on September 10, 2007 at 6:16 pm pinaldave

   @Dominator, @Rana,

   Please calm down.

   Rana, as I previously said, I have responsibility of running a large company and I have very little time.

   I write SQL to help most of the people. Your request is very much specific and with restricted need and I do not see how it can be helpful to many readers.

   I will be not able to comply your request. You will have to help yourself. Again, I am sorry, as you ordered I am not able to find sometime in weekend.

   Good luck,

   Regards,
   Pinal Dave

   
   
9. on September 12, 2007 at 11:11 am Rana

   Dear Pinal and Vistor Dominator,

   I wrote here because i was wondering that you must have done something before so i can use that.

   but no worries i have spent some time and get the solutions.

   Few algo i wrote.

   some counts and its really a wonderful solutions for even cleanign the data.

   I will be posting this in this weekend.

   I need to explain line by line otherwise people might not udnerstand.

   thanks anyway.

   
   
10. on September 20, 2007 at 9:35 am Brian

    Great tip; exactly what I was looking for. Thanks for posting it.

    
    
11. on October 4, 2007 at 8:31 am Alex

    I want the show a result that choose the line that has the lowest price

    What do you suggest

    Table

    Item Code Price

    Spider $10.00
    Spider $9.00
    Ant $5.00
    Ant $4.00

    The result I want to see would be
    Spider $9.00
    And $4.00

    Thanks

    
    
12. on December 4, 2007 at 11:08 pm Richard

    Thanks your solution was exactly what I was looking for. I had a bunch of ID’s with a different times of a code, that I need to know how many occurrences for each individual. Your code gave all that, and allow me to get the data to program accordingly.

    
    
13. on December 13, 2007 at 2:52 am kash

    It is great article. It real useful to me for quick result. Thank you, keep post this kind of articles.

    
    
14. on December 25, 2007 at 2:24 pm SQL SERVER - Delete Duplicate Records - Count Duplicate Records Links Journey to SQL Authority with Pinal Dave

    [...] SQL SERVER – Count Duplicate Records – Rows [...]

    
    
15. on January 17, 2008 at 4:17 am parviz

    Thank you, your solution worth 5 marks for my coursework. ;-)

    
    
16. on January 17, 2008 at 6:54 pm Dugi

    THis works for me very nice!

    Thnx!

    
    
17. on January 24, 2008 at 6:43 am Alex

    Hi Pinal,

    Thanks for the great info. I couldn’t believe how easy it is to accomplish what I was trying to do. I took what you wrote and modified it a bit since I only needed to know how many of each duplicate I had.

    
    
18. on January 29, 2008 at 11:44 am Sanjeev jha

    Thanks realy imagine

    
    
19. on March 18, 2008 at 8:10 am Tri Aji Yudista A

    For 11. Alex, you could do this perhaps:
    SELECT
    ITEM_CODE,
    MIN(PRICE) AS PRICE
    FROM TABLE
    GROUP BY ITEM_CODE
    ORDER BY ITEM_CODE

    
    
20. on April 4, 2008 at 5:41 pm Pravin

    Hi Pinal,

    This is really too good solution to find and delete duplicate records from database table.

    Thanks ,
    Pravin

    
    
21. on April 4, 2008 at 5:44 pm Pravin

    Hi Pinal,

    One more soultion to Delete completely similar record from database table

    We can play with row Id of the table row to delete a completely similar records , keeping one avilable using sysobjects in sql server 2005.

    Thanks,
    Pravin.

    
    
22. on April 20, 2008 at 10:28 pm Zahid

    Thanks a lot.

    
    
23. on May 12, 2008 at 3:21 pm supriya

    select sales6.Sou_Enqu,(count(sales5.project_reg)),(count(sales5.project_EMRB))from sales6,sales5 where sales5.sales_no=sales6.sales_no and sales5.project_reg =’Regent Park’and sales5.project_EMRB=’ECB’ Group by sales6.Sou_Enqu;
    this is my query i want count 10 field value in that query both field count value is displaed same.

    source regent park ecb eck
    newpeper 10 20 30
    email 20 10 20
    like that format

    plz help me soon
    tell me solution

    
    
24. on May 24, 2008 at 4:32 am ziozed

    Another solution is to put the table with duplicates in join with itself.
    For a simple example see:
    http://www.ugmfree.it/TipsTsql.aspx?tip=TipTsqlDeleteDuplicates

    
    
25. on July 1, 2008 at 3:53 pm takCare

    thx pinal…. it was very easy …

    
    
26. on August 18, 2008 at 12:56 pm Manoj

    Pinal,

    The query posted at the top of this page results with duplicate column and its count in descending order.
    How can i find the count the returned rows???

    Please help me.

    
    
27. on September 9, 2008 at 8:19 pm Jose

    Hi Pinal,
    I have a question.
    I need to loop through a table and if there are some duplicate values in one of the columns I need to add a suffix of a, b, c, and so on. But, there are different sets of duplicates. So, I need to do the suffix on each separate set of duplicates.
    Please notice bellow, there are three sets of values that are duplicates. This is a sample of my data.

    Could you give me any ideas on how to accomplish this task? Thank you.
    ZP19188AMR
    ZP19188AVE

    ZP19188AVH
    ZP19188AVH

    ZP19188COC
    ZP19188COC
    ZP19188COC
    ZP19188COC
    ZP19188COC
    ZP19188COC

    ZP19188PHM
    ZP19188PHM

    ZP19188RIM
    ZP19188SAB
    ZP19188WEH

    
    
28. on September 24, 2008 at 8:07 pm kris

    thanks for the post it really helped me.

    
    
29. on September 30, 2008 at 6:26 pm Rob

    Thanks. This is very helpful.

    
    
30. on October 14, 2008 at 5:56 pm Ashok

    I need to limit the number of duplicates. Can this be achieved? Thanks.

    
    
31. on October 23, 2008 at 5:47 pm Rajesh

    Thanks.

    
    
32. on November 13, 2008 at 11:09 pm padma

    HI Pinal,

    If I want to select all the fields in a table,
    How shold I write the query for that

    thank u

    
    
33. on December 13, 2008 at 1:09 am Baal

    thanks.

    
    
34. on December 30, 2008 at 11:49 am mahi

    select Taluk_Name_Eng ,count(Taluk_Name_Eng)as kk from tbl_PL04_Taluk

    what problem in this query

    
    
35. on January 1, 2009 at 6:45 pm EK

    Using the above Query is nearly just what I need but it only gives me an ID (YourColumn). I have another table which would have a named value for this ID. How can I return that instead? A Join or a nested statement?

    Many thanks

    
    
36. on January 1, 2009 at 7:04 pm EK

    Did it, quite new to SQL I’m afriad, hope it helps another novice :)

    SELECT top 20 computers.name as CID, COUNT(*) as ErrorCount
    FROM errors
    inner join computers
    on computers.id = errors.computerid
    GROUP BY computers.name
    HAVING COUNT(*) > 1
    ORDER BY COUNT(*) DESC

    
    
37. on January 2, 2009 at 10:30 am tejasnshah

    Hi,

    I have written to get Duplicate Rows from the table. You can use the ROW_Number() in SQL 2005.

    To read more about it:

    http://tejasnshah.wordpress.com/2008/12/31/find-duplicate-rows-with-row_number-sql-server-2005/

    Tejas

    
    
38. on January 21, 2009 at 8:55 pm Florin

    u r good

    
    
39. on February 4, 2009 at 5:50 pm MarioSanath(SriLankan)

    SELECT YourColumn, COUNT(*) TotalCount
    FROM YourTable
    GROUP BY YourColumn
    HAVING COUNT(*) > 1
    ORDER BY COUNT(*) DESC

    this query is solved my lot of problems
    Thank you very much

    
    
40. on February 24, 2009 at 5:39 pm SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

    [...] SQL SERVER – Count Duplicate Records – Rows [...]

    
    
41. on March 31, 2009 at 3:28 am Nathan

    If you’d like to get a total count of the duplicates as I needed to do, try returning the above query as a derived table and then do a sum on the count.

    SELECT SUM(Duplicates.TotalCount) FROM (SELECT YourColumn, COUNT(*) TotalCount
    FROM YourTable
    GROUP BY YourColumn
    HAVING COUNT(*) > 1) AS Duplicates

    My database has over 1200 duplicates. :(

    
    
42. on April 15, 2009 at 3:08 am Jai

    Hey Pinal,

    Excellent query ! thanks !

    
    
43. on April 16, 2009 at 3:36 pm yadav

    its really good update its help ful for every one thanks

    
    
44. on June 23, 2009 at 11:02 am Kami

    i have a data entry system for patient records. in my system there are several forms through which data is entered in different tables. and there is a form which takes the present condition of the patient (i call it PresentAssessment). now i what i want is when the user enters a new record i want to compare the values of the attributes (database table fields) of the newly entered patient and from all the matching records i want to calculate and return the chance(in terms of %age) of particular disease(s) (stored in PresentAssessment) to newly added patient. e.g. if i have two tables i.e. PatientDrugHistory and PresentAssessment. and say each of them already have 10 records with 10 columns each. now when entering the 11th record in PatientDrugHistory i want to calculte and store how much chances (in terms of %age) exist that this 11th patient is suffereing from disease1 (a column in PresentAssessment). please if anybody can tell me what query to write for this purpose