SQL SERVER – Delete Duplicate Records – Rows

Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3.

DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

Watch the view to see the above concept in action:

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

425 thoughts on “SQL SERVER – Delete Duplicate Records – Rows

  1. if there is no key in the table then what ?

    I have on table named [Duplicate] {ID int,FNAME varchar(10),MNAME varchar(10)}

    Here there is no key and here are duplicate rows. so hoca can i delete this duplicate rows.

    Check Data

    ID FNAME LNAME
    1 AAA CCC
    2 BBB DDD
    1 AAA CCC
    2 BBB DDD
    1 AAA CCC
    2 BBB DDD
    3 BCB DGD

    Remove duplicate rows and keep the data in to the table like this using single query.

    ID FNAME LNAME
    1 AAA CCC
    2 BBB DDD
    3 BCB DGD

    PLease if possible help because i faced this question in many interviews.
    Reply me

      • rno sname
        —————-
        1 Frieda
        1 Frieda
        1 Frieda
        4 Nathan
        6 senthil
        6 senthil
        6 senthil
        2 Senthil
        6 senthil
        1 Shanoj
        2 Shanoj
        4 Varun

        Step 1: Create duplicate table and move the duplicate value records of the original table to a duplicate table.

        SELECT DISTINCT * INTO DuplicateTb FROM dublicatetest GROUP BY sname,rno HAVING COUNT(rno) > 1

        After executed the above Query if you look the table the below result come.
        select * from DuplicateTb
        1 Frieda
        6 senthil

        select * from dublicatetest

        2 Shanoj
        1 Frieda
        2 Senthil
        4 Varun
        6 senthil
        1 Shanoj
        6 senthil
        4 Nathan
        6 senthil
        6 senthil
        1 Frieda
        1 Frieda

        Step 2: Delete all rows from the original table that also reside in the duplicate table.

        DELETE dublicatetest WHERE sname IN (SELECT sname FROM DuplicateTb)

        After executed the above Query if you look the table the below result come.

        select * from DuplicateTb
        1 Frieda
        6 senthil

        select * from dublicatetest
        2 Shanoj
        4 Varun
        1 Shanoj
        4 Nathan

        Step 3 : Move back the rows from duplicate table to original table.

        INSERT dublicatetest SELECT * FROM DuplicateTb

        After executed the above Query if you look the table the below result come.

        select * from DuplicateTb
        1 Frieda
        6 senthil

        select * from dublicatetest
        2 Shanoj
        1 Frieda
        6 senthil
        4 Varun
        1 Shanoj
        4 Nathan

        Step4: Drop the duplicate table.

        DROP TABLE DuplicateTb

        Dear friends now i hope u all are understood and got cleared.

        Good Luck
        Senthilkumar.T
        Be happy and try to make others happy

        • dear friends,

          here with i am sending a sample of code which might help u out in a easy manner

          select distinct * into newtable from oldtable

          truncate table oldtable

          insert into oldtable select * from newtable

          here we can have the distinct records or rows.

        • hi,

          how can we improve query performence. with same query dont use indexes view . for example select * from mytable query execute 2 min but the same query i want execute with in 1 min. hw can we do that one , please give me reply

      • dont worry man;
        make a call to me [phone number removed]

        select * from t1 where rowid=(select min(rowid) from
        t1 group by t1.c1)

      • delete from duplicate
        where fname in
        (
        select d1.fname
        from duplicate d1
        inner join duplicate d2
        on d1.fname=d2.fname and d1.Lname=d2.Lname
        group by d1.fname, d1.Lname
        having count (d1.fname)>1 and count(d1.Lname)>1
        )

    • [ Just Copy & Paste ]

      declare @TBL table(EmpID int)

      insert into @TBL(EmpID)
      values(90020),(90020),(90021),(90022),(90023),(90022),(90025),(90022)

      select * from @TBL order by EmpID

      ;with TBLCTE(EmpID,Ranking)
      AS
      (
      select
      EmpID,
      Ranking = DENSE_RANK() over (PARTITION BY EmpID order by newID())
      from @TBL
      )
      delete from TBLCTE where Ranking > 1

      select * from @TBL order by EmpID

      ————-

    • Dear pinal,

      If i am wrong then correct me.
      As per identity column in a table u will never have a duplicate rows in a table
      because it grows as 1,2,3,4,5
      so,we can’nt have duplicate rows in a identity column.
      but,we can have duplicate data in other column of a identity tables.

    • Value1 | Value2
      1 | ‘Mahesh’
      1 | ‘Mahesh’
      2 | ‘Rahul’
      2 | ‘Rahul’

      With NewData
      as
      (
      select s.*,DENSE_RANK() OVER
      (PARTITION BY s.Value1 order by newID() ) r from SampleData s
      )

      DELETE FROM NewData where r=2

  2. Good Question. I have been asking this question in interview many times to new candidates. Answer is:
    1) Add Identity Col and perform the operation displayed in this blog and drop Identity Col.
    2) User Cursor and save your values in temp table. If you receive the same row which you put in temp table delete it.
    3) You can use while loop as well do the same as cursor.

    Pinal Dave
    (http://www.sqlauthority.com)

    • Hi Pinal,

      Could you please answer ,what will be the exact query for Rahul’s posted question where he has listed an example.I am stuck with the same example.Provide One or More answers if possible and by writing the exact query.
      Thanks.

      • Hi Siddhatha,

        Pinal already mentioned steps for that.
        I would like to use ROW_NUMBER which is available from SQL SERVER 2005.

        DECLARE @Duplicate TABLE (
        ID INT,
        FNAME VARCHAR(10),
        MNAME VARCHAR(10)
        )

        INSERT INTO @Duplicate VALUES(1, ‘AAA’,’CCC’)
        INSERT INTO @Duplicate VALUES(2, ‘BBB’,’DDD’)
        INSERT INTO @Duplicate VALUES(1, ‘AAA’,’CCC’)
        INSERT INTO @Duplicate VALUES(2, ‘BBB’,’DDD’)
        INSERT INTO @Duplicate VALUES(1, ‘AAA’,’CCC’)
        INSERT INTO @Duplicate VALUES(2, ‘BBB’,’DDD’)
        INSERT INTO @Duplicate VALUES(3, ‘BCB’,’DGD’)

        –FOR SQL SERVER 2005 and above
        ;WITH CTE as(
        SELECT ROW_NUMBER() OVER(PARTITION BY ID, FName, MName ORDER BY (SELECT 1)) AS RowID,
        *
        FROM @Duplicate
        )
        SELECT ID, FName, MName
        FROM CTE
        WHERE RowID = 1

      • hei eddappa

        that the following code might be useful for your queries

        create view copy_duplicate
        as
        select name, f.name2, f.name3,……,count(id) from tablename
        group by name
        having count > 1

        or with CTE as

  3. Wouldn’t it be a little more simple to use Group By or Distinct to get the unique records in that situation?
    SELECT ID, FNAME, LNAME
    FROM Duplicate
    GROUP BY ID, FNAME, LNAME
    would give you the unique rows of Duplicate

    Rough Steps of one possibility to fix the table:
    1) Save the results of the group by query to a temp table
    2) Delete rows from the original table
    3) Fill original table with rows of temp table

    That you way you don’t have to use a cursor or change the design of table Duplicate. However, in the real world, you’d want to add a unique constraint to the ID column of table Duplicate to make sure it doesn’t get any more duplicates by ID.

    • If the version is 2005 or avoe, you can simply do

      delete t from
      (
      select row_number() over (partition by ID, FNAME, LNAME
      order by ID) , * from table
      ) as t
      where sno>1

      • Hi All,

        I have used table expression to remove duplicate records.

        Here is the solution

        Create Table emp
        (
        id int identity,
        empname varchar(20),
        empdept varchar(10)
        )

        insert into emp values(‘A’,’X’)
        insert into emp values(‘A’,’X’)
        insert into emp values(‘A’,’X’)
        insert into emp values(‘B’,’X’)
        insert into emp values(‘B’,’X’)
        insert into emp values(‘C’,’X’)

        select * from emp

        WITH Dublicates_Emp(empname, empdept,id)
        AS
        (
        SELECT empname,empdept,max(id)
        FROM EMP
        GROUP BY empname,empdept
        HAVING COUNT(empname)>1
        )

        delete from emp where id in
        (
        select emp.id
        from emp inner join Dublicates_Emp as d
        on emp.empname = d.empname
        and emp.empdept = d.empdept
        and emp.id d.id
        )

  4. Whoops, I almost forgot…

    Thanks for the post about deleting duplicates! Your code was easy find with a Google search. It’s exactly what I was needing. It’s simple and elegant! I sometimes find it difficult to think of how to do things like that, so it was nice to find your post about it!

  5. While searching for this solution I found many others which use cursors and complicated SQL statements but in the end they do the same thing as your simple & powerful solution. Thank you for posting it online!

  6. Seriously thanks,

    Keep plopping my head why i didnt come up with simplicity.

    – note
    Plopping is a merely profound and uncatastrophic way of activating the tiny brain members that are usually on vacation.

    – note 2
    nevermind the note

  7. Pinal,

    Once again you are the man! I looked at other sites to answer this question and I found this SQL statement to be the best and easiest. Now I know to just come straight here.

  8. If you are looking for deleting duplicates of more than 2 records, then the following code can be used…

    set nocount on
    drop table tbl1
    create table tbl1
    (
    col1 int
    )
    insert into tbl1 values(1)
    insert into tbl1 values(1)
    insert into tbl1 values(1)
    insert into tbl1 values(2)
    insert into tbl1 values(2)
    insert into tbl1 values(2)
    insert into tbl1 values(2)
    insert into tbl1 values(3)
    insert into tbl1 values(3)

    set rowcount 1
    select ‘start’
    while @@rowcount > 0 delete a from tbl1 a where (select count(*) from tbl1 b where a.col1 = b.col1)>1
    set rowcount 0
    select * from tbl1
    set nocount off

  9. If you are looking for deleting multiple records (duplicate with more than 2 records like 3,4,5 duplicate records, etc) and without key column then the following method can be adopted.

    Lets say your table is tab1.

    SELECT DISTINCT * INTO tab2 FROM tab1 — distinct of all columns

    DROP TABLE tab1

    EXEC sp_rename ‘tab2′,’tab1′

    Note:
    Take a back up of your original table before using dropping for your reference. You may need to grant permission of tab2 as given for tab1

    • hey guys u shld try it—————————–

      ————delete duplicate record————-if we have table Male wd duplicate record just
      step 1– transfer distinct recoed of male table to female

      insert into Female(Name)
      select distinct Name from Male
      step 2–drop table Male
      step 3– Rename table female as Male

  10. Simple is good! You Rock!

    (I found so many answers elsewhere and it’s incredible how 6 lines of code and one push of a button can do with your code)

  11. Ravi,
    your solution seems to be excelent but what about triggers and indexes defined on that table tab1.
    will they remain intact??
    Please repsond ….

    Amit

  12. Well to answer Rahul’s question and modify Ravi’s and Pinal Dave query…
    Here is a single query which will answer all your questions but this still needs Primary key or identity column to be added to the table though.
    DELETE FROM MyTable
    WHERE EXISTS (
    SELECT * FROM MyTable AS b
    WHERE
    b.[col1] = MyTable.[col1]
    AND b.[col2] = MyTable.[col2]
    AND b.[col3] = MyTable.[col3]
    GROUP BY
    b.[col1], b.[col2], b.[col3]
    HAVING
    MyTable.[ID] > MIN(b.[ID])
    )
    If you want most recent records to be present in your DB and delete multiple Old duplicate records, You can make use of
    DELETE FROM MyTable
    WHERE EXISTS (
    SELECT * FROM MyTable AS b
    WHERE
    b.[col1] = MyTable.[col1]
    AND b.[col2] = MyTable.[col2]
    AND b.[col3] = MyTable.[col3]
    GROUP BY
    b.[col1], b.[col2], b.[col3]
    HAVING
    MyTable.[ID] MIN(b.[ID])
    )
    Hope this answers all your questions.
    If you dont have Primary key or Identity Column then you can always create it and run this query and delete the Primary Key or identity column.
    “there are always better things to do to make lives easier.”
    enjoy your day
    Vamshi

  13. IN SQL SERVER 2005, This can be easily achieved without crating unique identifier by using CTE and ROW_NUMBER (), the modified query for sql server 2005 goes here
    ***********************************************
    WITH T1 AS (SELECT ROW_NUMBER ( ) OVER ( PARTITION BY ID, FNAME, LNAME ORDER BY ID ) AS RNUM FROM DUPLICATE )
    DELETE FROM T1 WHERE RNUM > 1
    ***********************************************
    To get a detail grasp on ROW_NUMBER () OVER () … Refer MSDN http://msdn2.microsoft.com/en-us/library/ms186734.aspx for.

  14. I have by mistake duplicated all my records.

    Through export/import wizard I selected my database (e.g pension) and copied tables to the desination, later I came to know that source and destination where the same.

    Therefore, in all the table all rows are duplicated.

    Can someone solve the problem.

  15. Pingback: SQL SERVER - Count Duplicate Records - Rows Journey to SQL Authority with Pinal Dave

  16. Thanks Ravi, your information helped. I modified the query in the following manner and it worked fine:

    Select distinct * into temp from tab1;
    Delete tab1;

    Insert tab1 Select * from temp;

    drop table temp;

  17. how can we delete duplicate rows if table doesn’t have any identity column and requirement is you don’t have to insert one column as identity and you don’t have to use cursor and you don’t have to use temp table. so query should be a single

    Please reply

  18. Dipak,

    What in application makes you have this many requirement?
    I would do what I have suggested in initial post or add identity column and drop it afterwards.

    Regards,
    Pinal Dave (SQLAuthority.com)

    • Pinal rather than adding a ID col and dropping it I think WHILE loop (which you already suggested) will be better opeion and even better than that is use ROW_NUMBER() function and simply delete the records which has row_number count > 1

      select row_number() over (partition by col order by col from tbl) and just delete where row_number() is greater than one and if one can set rowcount 1 also so if 2 records are exactly identical only 1 will be deleted….

      thanks

  19. this question i have asked in one of my interview, and requirement is like delete record should be in single query its ok if you can write subquery, but without use of temp table, without use cursor, and without use of insert identity column. still i am waiting for the answer,

  20. I’m trying to keep only the recent SaleDate in the following table. How can I do that?

    ID SaleDate SaleAmount
    40 6/23/2003 242
    40 12/28/2001 212
    40 6/13/1994 111
    41 11/30/2001 233
    41 10/25/1996 15
    41 4/21/1994 132
    42 6/17/2005 2765
    42 5/14/1994 147
    43 5/9/1994 145

    Thanks much

  21. Tayeb below query should work for you

    DELETE FROM #tempTab WHERE
    EXISTS
    (SELECT ID FROM #tempTab AS b WHERE #tempTab.ID = b.ID
    GROUP BY b.ID
    HAVING #tempTab.SaleDate < MAX(b.SaleDate))

  22. Depak, to answer your question here is a solution in SQL Server 2005

    CREATE TABLE #Table1 (col1 int, col2 int)
    INSERT INTO #Table1 VALUES (1, 1000)
    INSERT INTO #Table1 VALUES (2, 2000)
    INSERT INTO #Table1 VALUES (2, 2000)
    INSERT INTO #Table1 VALUES (3, 3000)
    INSERT INTO #Table1 VALUES (3, 3000)
    INSERT INTO #Table1 VALUES (4, 4000)

    SELECT * FROM #Table1;

    WITH T1 AS (SELECT (ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col1)) AS RNum FROM #Table1)
    DELETE FROM T1 WHERE RNum IN (SELECT a.RNum FROM T1 AS a, T1 AS b WHERE a.RNum > b.RNum GROUP BY a.RNum)

    SELECT * FROM #Table1

    DROP TABLE #Table1

    ROW_NUMBER() will solve your purpose.

  23. Unfortunately, this query doesn’t seem to work when attempting to delete a row that has duplicate id’s but different data.

    Can’t believe how hard it is to find something simple that will work.

  24. Why don’e we do a self union to the table.

    select * from mytable union select * from mytable,

    thsi query returns the exact records, and i guess we can use this data to build a new table or replacing the previous one.

    Thoughts on this please

  25. I am trying to design an query which functions like ICETOOL of mainframe it also requires this kind of duplicate deletions…

    ICETOOL in my application (sorts, merges and separates) duplicates and uniques from 4 input file into two output files(one containing unique ids and other containing its duplicate ids with different data) and discards the duplicates which occurs more than twice…

    i have the 4 files as tables now i need to get two output tables

    help will be much appreciated………

  26. How would your solution work if I had a single table called Customers that looks like this:

    ID LastName FirstName
    1 Smith John Adam
    2 Smith John A.
    3 Jones Jane Mary
    4 Jones Jane M.

    No other fields are in this table. Just what you see. The ID field is unique for each record. In this example, both records for Smith are the same person. The same goes for Jones. I want to view in a table the resulting unique records (i.e. only one record for John Smith, and one record for Jane Jones).

    I am a complete newbie and would appreciate guidance.

    Thanks!

  27. If you have Key already, you have seen many solutions in this post before, you need to group by on the repeating/columns that can be duplicated and delete them. The answered solution is if you dont have a unique key in the table.

    well to add more to that, once you do self union and insert into temp table, you can delete every thing form main table and insert every thing from temp table in to the original/main table. I think this should help..

    -Aditya

  28. SELECT id,name,mNumber,sNumber,partOfSpeech,type FROM keyword where id IN (Select max(id) from keyword GROUP BY name)

    The above is if you want to just select a column without duplicates in it.

  29. create table T1 (C1 int, C2 varchar(3))

    –Fill it with duplicate Records

    declare @as Table (c1 int,c2 varchar(3))
    insert into @as select Distinct * from T1
    Truncate table T1
    Alter Table T1 Disable Trigger All
    insert into T1 select * from @As
    Alter Table T1 Enable Trigger All

  30. Thanks Mr. Dave for the codes.
    I’ve searched inside out in the internet but still couldn’t find a solution for my problem.
    Your code manage to solved it, your truly a master in SQL. Thanks again

  31. Can you show how to make this a select Into, so that one can save the deleted records in another table

    DELETE
    FROM MyTable
    WHERE ID NOT IN
    (SELECT MAX(ID)
    FROM MyTable
    GROUP BY DuplicatevalueColumn1, DuplicateValueColumn2, DuplicateValueColumn2

    • select * into newtable
      FROM MyTable
      WHERE ID NOT IN
      (SELECT MAX(ID)
      FROM MyTable
      GROUP BY DuplicatevalueColumn1, DuplicateValueColumn2, DuplicateValueColumn3

  32. I thing this one alos workout
    ====================
    set rowcount 1
    delete test
    from test a
    where(select count(*) from test b where b.age = a.age and b.name = a.name)
    > 1
    while @@rowcount>0
    delete test
    from test a
    where(select count(*) from test b where b.age = a.age and b.name = a.name)
    >1
    set rowcount 0

  33. //Here n is maximum allowable number of a record
    create or replace procedure t_dili1_2
    (n in int,q in out sys_refcursor)
    as

    begin

    open q for

    select a,b
    from dil1 where (a,b) in(
    select a,b from (select a,b,count(*) from t group by a,b having count(*)<n));

    end t_dili1_2;
    /

    variable z refcursor

    exec t_dili1_2(4,:z)

    print z

  34. Plz Reffer to my (above) comment (PL/SQL Block)..
    Here dil1 is name of TABLE.
    a,b are columns with repeated records.
    I did is Using REFCURSOR.

  35. Q: Display all the records of a table t (where there are several duplicated & reduplicated records present) with restricted number (n) of distinct records. (Use REFCURSOR)

    Solution:
    create or replace procedure t_dili1_2
    (n in int,q in out sys_refcursor)
    as

    begin

    open q for

    select a,b
    from t where (a,b) in(
    select a,b from (select a,b,count(*) from t group by a,b having count(*)<n));

    end t_dilip1_2;
    /

    variable z refcursor

    exec t_dili1_2(4,:z)

    print z

  36. Hello All,
    I have a situation where I have two tables A & B. A has the actual data, table B maintains a sequence of data that is in table A. I have a process that polls table A every 5 seconds. Whenever a record is updated in table A with a TRANSID n, table B is updated with the same TRANSID. This said, what I would like to achieve is, when I poll table A, I should get all the records that were updated in the past 5 seconds. The catch is, same record can be updated more than once and end up updated with a greater TRANSID, table B is updated with the same TRANSID, but if same record is updated, one of their columns REPORT_ID, would be the same in table A(REPORT_ID not in table B). I should be able to get the most recent of the duplicate records and other records that were updated. Any help is greatly appreciated since writing SQL queries is not one of my fortes.

  37. sir,
    i am very much imperssed with u r ans. but if no of columes will be more than 100 then what is the procedure
    please repaly to this one to my mail id if possible

  38. Hi,

    I need to delete all the duplicate records with MAPID being the duplicated FIELD where ADDRESSCOUNT = 0 being the other defining criteria. My table looks like;

    ID MAPID ADDRESSCOUNT
    111 54560 4
    132 54560 0
    198 23429 1
    240 29584 1
    248 29584 0

    Any help appreciated.

    Using MS SQL Server 2000.

    Regards,

    Jim

  39. Hi jim(54),

    this should work for you.

    DELETE
    FROM duptest
    WHERE MAPID IN
    (select MAPID
    from duptest
    group by mapid
    having count(mapid) > 1) and AddressCount = 0

    cheers,
    anand.

  40. in case of no id column in table one can delete duplicate rows as below

    create view abc as select *,row_number() OVER (PARTITION BY dupcol1,dupcol2,… ORDER BY dupcol1,dupcol2,…) as rnum from
    table

    delete from abc where rnum > 1

    drop view abc

  41. Hi All,

    I would like to share one suggestion that is :
    should’t We conclude each topic with one best answer (if we can).

  42. Hello Sir,

    I recently joined your site, and found it really very helpful.

    How about using ‘ROWID’ to delete the duplicate rows.
    Please check this query.

    DELETE
    FROM MyTable
    WHERE ROWID NOT IN
    (SELECT MIN(ROWID)
    FROM MyTable
    GROUP BY DUPL_COL1,DUPL_COL2,DUPL_COL3)…
    –All col names

    Now, my question is that if I have more than two duplicate records I want to keep 2 of them and to remove rest.
    How can I do it?

    Please help out.

    Thank you.

  43. Hi ATIN(59),

    For finding only second highest salary – -

    select * from
    (select * from employee orderby salary desc
    where rownum>=2)
    minus
    select * from
    (select * from employee orderby salary desc
    where rownum>=1) ;

    And to get Only Top 2 Highest salary –

    select * from
    (select * from employee orderby salary desc
    where rownum>=2);

    Thanks & Regards
    Angadi Doddappa

  44. Hi,

    This page looks really cool, hope I will get answer for my question, I have a table with 35 columns and have duplicate rows based on 6 columns. So how do I remove duplicates and keep the original rows in the table, keep in mind table has around 500,000 rows.

  45. Hi, please help me…. I couldn’t find answer for this anywhere…

    I have a table with a column having data type varchar and it has data with numbers and alphabets in it. I want to replace the data in the column with ‘ ‘ where there are alphabets.
    (REPLACE((column),’S’,”)) — I know this would replace s with ‘ ‘, but how to replace all the alphabets with ‘ ‘.

    Please answer, I urgently need the answer.

    Thanks to all in advance….

  46. Hellooo

    I have a table for example EMployees, it has a PID which is auto number, and other 2 fields one EmployeeID and other is Name.

    what i want is to delete duplicated data in Employee ID and Name for sure, what to do plz….

    Thanks

  47. DELETE
    FROM EMployees
    WHERE Employee ID IN
    (select Employee ID
    from EMployees
    group by EMployees
    having count(EMployees) > 1)

    just try this……;

  48. this question i have asked in one of my interview, and requirement is like delete record should be in single query its ok if you can write subquery, but without use of temp table, without use cursor, and without use of insert identity column. still i am waiting for the answer,

    Pls give solution as in sql server 2000

  49. Hi
    I am having a problem in generating reports from two SQL tables.
    eg.
    table1.
    loc1 10
    loc2 10
    loc3 40

    Table2
    loc1 xx
    loc1 yy
    loc1 zz
    loc3 pp

    I want to print.

    Table1 + table2
    loc1 10 loc1 xx
    null loc1 yy
    null loc1 zz
    loc2 10 null
    loc3 40 loc3 pp

    Any way to generate this using joins or any other.Pl. advise

    pankaj

  50. Great solution, however….i have a table that has…………..

    1] Duplicate Id’s
    2] Unique id’s

    Now on the basis of your query i would be able to delete the duplicate id’s(and records corresponding to that is)..but that would delete all subsequent unique id’s as well.

    So basically i wanto find all duplicates, choose one, delete the rest and keep the unique records as well.

    Could you or anyone please help me find a solution to this…

    Thanks!

  51. Hi ,

    Pls help in this issue .
    create table test1
    ( A int , B varchar(2) , C varchar(20) , D int , E varchar(20))

    insert into test1 values (1,’A’,’aaa’ , 111, ‘adf’)
    insert into test1 values (2,’B’,’bbb’ , 222, ‘adsf’)
    insert into test1 values (3,’C’,’ccc’ , 333, ‘cfe’)
    insert into test1 values (1,’A’,’aaa’ , 444, ‘vgd’)
    insert into test1 values (4,’D’,’ddd’ , 555, ‘qef’)

    Now for the above table Columns A , B and C (combined) form the unique identifiers for a given record.

    I would like a query to retrieve the unique records(identified by columns A , B and C in the table )
    i.e
    2,’B’,’bbb’ , 222, ‘adsf’
    3,’C’,’ccc’ , 333, ‘cfe’
    4,’D’,’ddd’ , 555, ‘qef’

    Constraint being that i cannot add any composite / identity for the three columns (A,B ,C)

    Any help is highly appreciated……

  52. My solution is:

    select * from DUPLICATE
    union
    select * from DUPLICATE

    This query will filter out all duplicate records from DUPLICATE table

    Tarun

  53. 71.
    Hi, please help me…. I couldn’t find answer for this anywhere…

    I have a table with a column having data type varchar and it has data with numbers and alphabets in it. I want to replace the data in the column with ‘ ‘ where there are alphabets.
    (REPLACE((column),’S’,”)) — I know this would replace s with ‘ ‘, but how to replace all the alphabets with ‘ ‘.

    Please answer, I urgently need the answer.

    The Answer is as follow :
    You have to use Stuff function in place of Replace.
    e.g.

    select stuff(column,1,len(column),”) from Table_name

    A.K.Rastogi

  54. This soluton worked perfectly in my situation! I had read many other suggestions online but most of them were overly complicated and usually required creating additional tables. Thanks for posting this!

  55. Hi Pradeep,

    Today I saw your question. It’s late, but if you haven’t find any solution below may help…
    ____________________________________________________________________________________________
    SELECT * FROM test1 t1, (SELECT A, B, C FROM test1 GROUP BY A,B,C HAVING COUNT(1)>1) t2
    WHERE t1.A t2.A AND t1.B t2.B AND t1.C t2.C
    ____________________________________________________________________________________________

    Regards
    Saroop

  56. ____________________________________________________________________________________________
    SELECT * FROM test1 t1, (SELECT A, B, C FROM test1 GROUP BY A,B,C HAVING COUNT(1)>1) t2
    WHERE t1.A <> t2.A AND t1.B <> t2.B AND t1.C <> t2.C
    ____________________________________________________________________________________________

  57. hi dave,
    i m really imprssd with ur blog. n u knw d bst part is u rply to each possible post.
    hoping to get best out dis blog
    thanks
    and all d best
    deepti

  58. Hi all,

    i have doupt ..plz clarify that.

    create table a(empid int)
    create table b(empid1 int)

    insert into a values(1)
    insert into b values(1)

    insert into a values(2)
    insert into b values(3)

    output:
    empid
    ———–
    1
    2

    empid1
    ———–
    1
    3

    with out using temporary table to del the comman record from separate two table.

  59. Hi All,

    Those who have requested for
    How to get 1st, 2nd, 3rd, 4th, nth topmost salary from an Employee table

    The following solution is for getting 6th highest salary from Employee table ,

    SELECT TOP 1 salary FROM (SELECT DISTINCT TOP 6 salary FROM employee
    ORDER BY salary DESC) a ORDER BY salary

    You can change and use it for getting nth highest salary from Employee table as follows

    SELECT TOP 1 salary FROM (SELECT DISTINCT TOP n salary FROM employee
    ORDER BY salary DESC) a ORDER BY salary

    where n > 1 (n is always greater than one)

  60. I need to be able to select only the record with the max(datetime) of the same guid. Is this possible? I only want the record with the date of 6:53:02 and not 5:07:32 and I want to keep the record with 5:07:19 (different guid)

    b16569a1-e3dc-4f03-b2a0-bf3e2677c466 rptProviders_Provider2Location 4/13/2008 5:07:19 AM 4/11/2008 6:46:29 PM

    36b6419a-a3a5-49ad-b84d-942c89c4754c rptProviders_Provider2Location 4/13/2008 5:07:32 AM 4/11/2008 6:46:29 PM

    36b6419a-a3a5-49ad-b84d-942c89c4754c rptProviders_Provider2Location 4/13/2008 6:53:02 AM 4/11/2008 6:46:29 PM

  61. Here is the query I have so far:

    SELECT P2L.ProviderId, dbo.AUDIT.TableName, dbo.AUDIT.OccurredAt, P2L.xLastUpdated
    FROM dbo.rptProviders_Provider2Location AS P2L WITH (NOLOCK) INNER JOIN
    dbo.rptProviders_Provider2LocationCategory AS P2LC WITH (NOLOCK) ON P2L.xId = P2LC.Provider2LocationId INNER JOIN
    dbo.AUDIT ON P2L.xId = dbo.AUDIT.RowId

  62. I’ve got a slightly different problem. I’m creating a stored procedure which will retrieve several different columns from a hospital table (month, year, patient name, intervention hours, and date changed) Those aren’t the real column names; the idea is that records are kept when a patient’s behavior requires staff intervention. The records are entered monthly, but frequently–in a given time range–the reports show patients’ names more than once, because people update the intervention time numbers (stuff is loaded in wrong, then corrected, or loaded in as 0 to meet a deadline, then updated, etc). I need to get rid of the duplications, meaning that when names are shown more than once for a given month and year, I only want one of the duplicated intervention records to show up. I want to filter it by “date changed,” meaning the most recent entry for each patient.

    So when I run an “interventions” report for specified time frame at a given hospital, I want to see the intervention hours broken down by patient name (but I only want to see the most recently entered record for each patient who had intervention activity during the time parameters of the report).

    How can I modify my SELECT statement to do this?

  63. how to solve this problem?

    ID(identity column) name
    ——————————————

    1 david
    2 john
    3 ram
    4 paul
    5 aish
    6 raj
    7 mani
    8 madu
    9 cotter

    now i deleted the 4′th row

    4 paul

    ok

    now how can i display the remaining names as given below

    ID(identity column) name
    ——————————————

    1 david
    2 john
    3 ram
    4 aish
    5 raj
    6 mani
    7 madu
    8 cotter

    any body can help me sir?

    saranpselvam@gmail.com

  64. This works in 2000. WARNING: According to MS, SET ROWCOUNT will not work with INSERT, DELETE, and UPDATE in later versions.

    – Create table with a number of values between zero and nine
    select a+b+c as val
    into dbo.rmtemp
    from (select 0 a union all select 1 union all select 2 union all select 3) a
    , (select 0 b union all select 1 union all select 2 union all select 3) b
    , (select 0 c union all select 1 union all select 2 union all select 3) c

    – Show what you’ve got
    select val,count(*) row_count from dbo.rmtemp group by val

    – Limit processing to a single row
    set rowcount 1
    – While you’ve got duplicates, delete a row
    while (select top 1 val from dbo.rmtemp group by val having count(*) > 1) is not null
    delete from dbo.rmtemp where val in (select top 1 val from dbo.rmtemp group by val having count(*) > 1);
    – Remove single row processing limit
    set rowcount 0

    – Confirm that only uniques remain
    select val,count(*) row_count from dbo.rmtemp group by val

    – Clean up
    drop table dbo.rmtemp

    – Comment
    I knew there had to be a better way.

  65. Pingback: SQL SERVER - 2000 - SQL SERVER - Delete Duplicate Records - Rows - Readers Contribution Journey to SQL Authority with Pinal Dave

  66. hi pinal dave ,

    i have a question for u , i want to retrive the rows from a table by avoiding duplicate rows with all coloumns .i.e; duplicate row sholud be retrieved only once . any help reg…

  67. Hi Pinal,

    Thanks for your tips.

    I have a question. We have a table that have many contacts that are duplicated more than once. And we need a query that excludes all duplicated people who has already registered is there a more efficient way of doing it than this?

    SELECT CD.email
    , CD.contactid
    FROM contact CD
    WHERE (CD.opt_out = 0)
    AND (CD.email ‘NoEmail’)
    AND (CD.email IS NOT NULL)
    AND (CD.email ”)
    AND (CD.registered = 0)
    AND Lower (CD.email) + ‘||’ + Lower (cd.firstname) + ‘||’ + Lower (CD.lastname) NOT IN
    (SELECT Lower (CD1.email) + ‘||’ + Lower (CD1.firstname) + ‘||’ + Lower (CD1.lastname)
    FROM contact CD1
    WHERE (CD.registered = 1)
    )
    GROUP BY CD.contactid
    , CD.email

    Thank you,

    EstebanD

  68. Dear Sir,
    i a have a table with id as one column,name as another column , in name there r diffrent names is there is possible to display all the alphabets in the name column.it is very urgent.where i can see mu solution if else post it to my id.

  69. I am having a query inside the view which is returning more than one value. I need all the values to be returned in a separate row while running the view. Can ypu provide me a solution?

  70. Dear sir
    I am faceing a problem in sql query.
    I have two table.
    1st table is maste table, In master table total 180 records are stored.
    our requirement total 180 rows – 20 rows are stored in 2nd table, then display 160 mark as ‘N’ bcos stored in master table and other records are mark as ‘Y’ bcos its stored in 2nd table.

    Table 1st
    AppID FunCode SubFuncode FunName SubFunName Mark
    Dis 1 30 Main 0 N
    Depos 2 30 &Main 0 N
    Lox 3 30 &Main 0 N
    LCredit 4 30 &Main 0 N

    In 2nd table same records but only 20 records are stored.
    AppID FunCode SubFuncode FunName SubFunName Mark
    Dis 1 30 &Main 0 Y
    Depos 2 30 &Main 0 Y

    In query we try to fetch all Y marked row and N marked with Union query.

    SELECT TBLAPPLICATIONS.Name, TBLGROUPFUNCTIONS.AppID, TBLGROUPFUNCTIONS.FunctionCode, TBLALLFUNCTIONS.DefaultSubFunctionName,
    TBLGROUPFUNCTIONS.SubFunctionCode, ‘Y’ AS AssignedFunction
    FROM TBLGROUPFUNCTIONS INNER JOIN
    TBLALLFUNCTIONS ON TBLGROUPFUNCTIONS.AppID = TBLALLFUNCTIONS.AppID AND
    TBLGROUPFUNCTIONS.FunctionCode = TBLALLFUNCTIONS.FunctionCode AND
    TBLGROUPFUNCTIONS.SubFunctionCode = TBLALLFUNCTIONS.SubFunctionCode INNER JOIN
    TBLAPPLICATIONS ON TBLGROUPFUNCTIONS.AppID = TBLAPPLICATIONS.AppID
    WHERE TBLGROUPFUNCTIONS.GroupID = 12
    UNION ALL
    SELECT TBLAPPLICATIONS.Name, TBLALLFUNCTIONS.AppID, TBLALLFUNCTIONS.FunctionCode,
    TBLALLFUNCTIONS.DefaultSubFunctionName, TBLALLFUNCTIONS.SubFunctionCode, ‘N’ AS AssignedFunction
    FROM TBLALLFUNCTIONS INNER JOIN TBLAPPLICATIONS ON TBLALLFUNCTIONS.AppID = TBLAPPLICATIONS.AppID
    WHERE
    (TBLALLFUNCTIONS.AppID NOT IN (SELECT AppID FROM TBLGROUPFUNCTIONS WHERE GroupID = 12) OR
    TBLALLFUNCTIONS.FunctionCode NOT IN (SELECT FunctionCode FROM TBLGROUPFUNCTIONS WHERE GroupID = 12) OR
    TBLALLFUNCTIONS.SubFunctionCode NOT IN (SELECT SubFunctionCode FROM TBLGROUPFUNCTIONS WHERE GroupID = 12))
    Order BY aPPID,FunctionCode,sUBfUNCTIONcODE

    From this query record is not show correctley.
    Please please immediate reply me.
    I am waiting your reply please suggest me.
    Asit Sinha

  71. Hi,

    I have an issue with and sql query i’m trying to get right and I seem not to find the answer.

    I have a table with 4 colums: id1,id2,value1,value2.
    I need to select all rows with a unique pair of (id1,id2) and use a 3rd column in the select for something like : sum(value1*value2) for duplicate rows.

    So basicaly, show 1 row for each pair and next get a sum of all the values for that pair (sum the duplicate rows on value1,value 2).

    I only been able to select the unique pairs until now using a group by id1,id2 but i’m having troble with the sums of the duplicate rows for each pair.

    I’d appreciate your help

  72. Hi Bogdon,

    It would have been easy to provide solution if you would have provided proper Input and desired output. Anyways see it the below works for you based on my understanding

    CREATE TABLE #T1 (ID1 INT, ID2 INT, Value1 INT, Value2 INT)
    INSERT INTO #T1 VALUES (1,1,5,10)
    INSERT INTO #T1 VALUES (1,1,6,12)
    INSERT INTO #T1 VALUES (1,1,50,100)
    INSERT INTO #T1 VALUES (1,2,7,14)
    INSERT INTO #T1 VALUES (1,2,8,16)
    INSERT INTO #T1 VALUES (2,1,2,5)
    INSERT INTO #T1 VALUES (2,1,3,6)
    INSERT INTO #T1 VALUES (2,3,10,20)
    INSERT INTO #T1 VALUES (2,3,25,50)

    SELECT * FROM #T1;

    SELECT ID1, ID2, SUM(Value1*Value2) FROM #T1 GROUP BY ID1, ID2;

    WITH T1 AS (SELECT (ROW_NUMBER() OVER (PARTITION BY ID1 ORDER BY ID1)) AS RNum, * FROM #T1 )
    DELETE FROM T1 WHERE Rnum IN (SELECT a.RNum AS S FROM T1 AS a, T1 AS b WHERE a.RNum > b.RNum GROUP BY a.RNum)

    SELECT * FROM #T1

    DROP TABLE #T1

    Post the input and desired output if the above is not what you want.

    Vamshi

  73. Sorry WITH statement is not yours…

    CREATE TABLE #T1 (ID1 INT, ID2 INT, Value1 INT, Value2 INT)
    INSERT INTO #T1 VALUES (1,1,5,10)
    INSERT INTO #T1 VALUES (1,1,6,12)
    INSERT INTO #T1 VALUES (1,1,50,100)
    INSERT INTO #T1 VALUES (1,2,7,14)
    INSERT INTO #T1 VALUES (1,2,8,16)
    INSERT INTO #T1 VALUES (2,1,2,5)
    INSERT INTO #T1 VALUES (2,1,3,6)
    INSERT INTO #T1 VALUES (2,3,10,20)
    INSERT INTO #T1 VALUES (2,3,25,50)

    SELECT * FROM #T1;

    SELECT ID1, ID2, SUM(Value1*Value2) FROM #T1 GROUP BY ID1, ID2;

    I dont know why you said GROUP BY didnt worked for you if the above is what you are expecting

  74. Hi Haneef and Preveen

    Hope this would help you in getting all those employees with Nth Highest salary

    DECLARE @n INT

    SELECT @n = 2 — Change N value to get Nth highest value

    CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)
    INSERT INTO #T1 VALUES (1, ‘Vamshi’, 1000)
    INSERT INTO #T1 VALUES (2, ‘xxxxx’, 2000)
    INSERT INTO #T1 VALUES (3, ‘yyyyy’, 3000)
    INSERT INTO #T1 VALUES (4, ‘zzzzz’, 4000)
    INSERT INTO #T1 VALUES (5, ‘sssss’, 5000)
    INSERT INTO #T1 VALUES (6, ‘ccccc’, 6000)
    INSERT INTO #T1 VALUES (7, ‘ppppp’, 2000)
    INSERT INTO #T1 VALUES (8, ‘aaaaa’, 4000)
    INSERT INTO #T1 VALUES (9, ‘bbbbb’, 5000)

    SELECT a.ID1, a.[Name], a.Salary
    FROM #T1 AS a
    WHERE (@n-1) = (
    SELECT COUNT(DISTINCT(b.Salary))
    FROM #T1 AS b
    WHERE b.Salary > a.Salary)

    DROP TABLE #T1

    Vamshi

  75. Hi,
    I need to Get a primary key in a particular table in MS-SQL SERVER 2000. How Can i get. Is there any query is available???

    Thanks in advance
    Mohan.V

  76. Pinal, great information on your site!

    I think Senthilnathan in comment #16 has the right solution that can be used for deleting duplicates and for finding the nth row in a group. The key is ROW_NUMBER() function with a Partition.

    – EXAMPLE TO GET THE 5th highest salary of the “Manager” group.

    – CREATE Common Table Expression CTE
    With MyCTE AS (
    SELECT
    ROW_NUMBER() OVER (PARTITION EmployeeType
    ORDER BY EmployeeType, Salary) AS OrderedGroupId,
    EmployeeId,
    EmployeeType,
    Salary
    FROM SalaryHistory)
    SELECT EmployeeId, — Query results of CTE
    Salary
    FROM MyCTE
    WHERE EmployeeType = “Manager”
    AND OrderedGroupId = 5

    This could also be a DELETE statement deleting duplicate items in a PARTITION (DELETE FROM MyCTE WHERE OrderedGroupId > 1)

  77. @Mohan,

    1. If you want to see the primary on a specific table then Execute this stored procedure,

    Sp_help table_name — you have to give table name

    It will display all the table properties, like columns, keys, indexes…
    In constraints section it will show you all the keys details, on which column primary key is made.

    2. If you want to see all the primary keys on all tables, then use this query,

    SELECT A.CONSTRAINT_NAME ‘CONSTRAINT NAME’ , A.COLUMN_NAME ‘COLUMN NAME’, A.TABLE_NAME’TABLE NAME’, A.ORDINAL_POSITION ‘POSITION OF COLUMN’
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS B, INFORMATION_SCHEMA.KEY_COLUMN_USAGE A
    WHERE B.CONSTRAINT_NAME =A.CONSTRAINT_NAME AND CONSTRAINT_TYPE = ‘PRIMARY KEY’
    ORDER BY A.TABLE_NAME, ORDINAL_POSITION
    – Run this script as is don’t change anything

    This will give you primary key name, on which column it is made and which table it is made and also position of the column.

    Sometimes, we create composite primary keys, meaning primary key on more than one column on one table in that case when you run the above query you will see table name more than once, dont get confuse, see the “position of the column” ( in the output) and then you will come to know that this table has a composite primary key.

    ** A table can have only one Primary key.

  78. Rahul ask how if there is no key in the table to delete the duplicate data,

    Please use this sql to do it, @ID, @Lname and @fname is depend on our field in the table:

    DECLARE @ID varchar(150), @lname varchar(150), @fname varchar(150), @MyCount int, @Counting Int

    DECLARE authors_cursor CURSOR FOR
    SELECT id, FNAME, LNAME, COUNT(*) AS myCount
    FROM dbo.Table1
    GROUP BY id, FNAME, LNAME HAVING (COUNT(*) > 1)

    open authors_cursor

    FETCH NEXT FROM authors_cursor
    INTO @ID, @fname, @LName, @MYCount

    WHILE @@FETCH_STATUS = 0 BEGIN
    DECLARE aut2 CURSOR local scroll dynamic OPTIMISTIC FOR
    SELECT *
    FROM table1
    WHERE id = @id AND lname = @lname AND fname = @fname
    OPEN aut2
    SET @counting = 1

    FETCH next FROM aut2
    begin

    WHILE @Counting < @MyCount BEGIN
    print @counting
    DELETE FROM table1 WHERE CURRENT OF aut2
    SET @Counting = @Counting + 1
    FETCH next FROM aut2
    END
    –end

    end
    CLOSE aut2
    DEALLOCATE aut2
    FETCH NEXT FROM authors_cursor
    INTO @ID, @Lname, @FName, @MYCount

    END
    CLOSE authors_cursor
    DEALLOCATE authors_cursor

  79. Answer for Rasmi, please study this one:

    declare @MyStr varchar(150), @pjg int

    DECLARE authors_cursor CURSOR FOR
    SELECT LNAME
    FROM dbo.Table1

    open authors_cursor
    FETCH NEXT FROM authors_cursor
    INTO @mystr

    WHILE @@FETCH_STATUS = 0 BEGIN
    set @pjg=len(@mystr)-1
    while @pjg>=0
    begin
    print substring(@mystr,len(@mystr)-@pjg,1)
    if isnumeric(substring(@mystr,len(@mystr)-@pjg,1))=0

    set @mystr=replace (@mystr,substring(@mystr,len(@mystr)-@pjg,1),”)

    set @pjg=@pjg-1
    end
    print @myStr
    update table1 set lname=@mystr where current of authors_cursor
    FETCH NEXT FROM authors_cursor
    INTO @mystr
    end
    print @myStr

    CLOSE authors_cursor
    DEALLOCATE authors_cursor

  80. In most cases, two duplicate records in a table may not be the same. For example, a customer may enter his name or address, or phone number in different ways, but only from his email, we know this is the same person.

    My question is, if a table contains duplicate records like this, and I only want to keep latest record of the dups. I mean, based on customer’s email, and the date of registration, can we have a way to delete the duplicate records? for example:

    table_customers contains “email”, “name”, and “date”:

    ab@c.com John Smith 24/8/2006
    ab@c.com John S. 12/9/2007

    I only wants to keep the latest record if two records have the same email, no matter if the names are the same or not.

    Thank you for your help.

    Mike

  81. For Mike may be you can use this Query:

    DELETE table_customers
    FROM (SELECT Email, MAX(Date) AS Date
    FROM table_customers
    GROUP BY Email
    HAVING Email = ‘ab@c.com’)) DERIVEDTBL
    WHERE table_customers.Email = DERIVEDTBL.Email AND table_customers.Date DERIVEDTBL.Date

  82. Hi

    I have a question. Below is the Employee table
    EmpID EmpName Salary
    ———– —————————– ———-
    1 Aashish 15000
    1 Aashish 15000
    3 Gunjan 25000
    3 Gunjan 25000
    5 Atul 35000
    6 Animesh 20000

    Now I have to keep only one record either of the two and remove the duplicate record. Condition is There should not be any use of temp tables, no primary keys, no identity columns, no cursors. Everything has to be handeled in a query . And Database is MS SQL Server 2000

  83. For Aashish Mangal, You Can Use Query Analyzer Like This:

    ALTER TABLE [Employee] ADD [MySpecialIdx] [int] IDENTITY (1, 1) NOT NULL

    Go

    DELETE [Employee]
    FROM (SELECT EmpID, EmpName, Salary, MAX(MySpecialIdx) AS mySpecialIdx
    FROM [Employee]
    GROUP BY EmpID, EmpName, Salary)) DERIVEDTBL
    WHERE table_customers.EmpID = DERIVEDTBL.EmpID AND [Employee].EmpName = DERIVEDTBL.EmpName AND AND
    [Employee].EmpName = DERIVEDTBL.EmpName AND Employee.myspecialIdx DerivedTbl.myspecialidx

    Go

    ALTER TABLE [Employee] DROP COLUMN MySpecialIdx

    Go

    Or You Can Use In View and Execute Step By Step

  84. The Next for Aashish Mangal, we can use this query to delete the duplicate record:

    WHILE (SELECT TOP 1 COUNT(id) AS Expr1
    FROM dbo.Table1
    GROUP BY id, FNAME, LNAME
    HAVING (COUNT(id) > 1)) > 1
    BEGIN
    set rowcount 1
    DELETE table1
    FROM (SELECT TOP 1 id, FNAME, LNAME
    FROM dbo.Table1
    GROUP BY id, FNAME, LNAME
    HAVING (COUNT(id) > 1)) DERIVEDTBL
    WHERE table1.id = derivedtbl.id AND table1.fname = derivedtbl.fname AND table1.lname = derivedtbl.lname
    END

    ==
    If this query will be saved, the first time you must add any table to query than remove the query text and replaced with the above query text, then you can saved this query with your desired name.

  85. Hi Pinal,

    I had the same query i tried using Rank() function.

    It worked for me.

    Attaching code here:

    WITH DeleteDuplicates
    AS
    (
    SELECT RANK() OVER (PARTITION BY DUPFIELD ORDER BY PKFIELD ) AS RANK, * FROM TABLENAME
    )
    DELETE FROM DeleteDuplicates WHERE RANK > 1

    Wish add more value to the topic.

    Thanks,

    Mit_2807

  86. hi sir i did like this to delete duplicate rows…

    sir i accomplished this using rank functions… and CTE

    1)create table emp (id int,name varchar(20))

    2) i inserted 1,’rakesh ‘ 3 times
    and 2,’sagar’ 2 times

    deleting duplicate rows…

    with cte
    as
    (
    select id,name,rank() over(order by id) r,row_number() over(order by id) rn from emp
    )
    delete from cte
    where r rn

    will this query degrade the performance or not ……

  87. Dear All,

    i have a table called Player_details in that i have a data’ like below without any key i want to delete one record and another in data how to do this.

    100 chennaiRoyals 9841998470
    101 DareDevils 9984725487
    101 DareDevils 9984725487

    Both are same as like.

    can u pls help me

  88. To Find the 2nd Highest Salary:

    2nd highest salary

    1)SELECT max(salary) FROM Employee
    WHERE salary < (SELECT max(salary) FROM employee)

    Purushot

  89. your query works great !!! finally I found an elegant way to solve the problem without exploiting new sql commands which in SQL 2005 don’t exist yet.

  90. Dear All,

    i have a table called Player_details in that i have a data’ like below without any key i want to delete one record and another in data how to do this.

    100 chennaiRoyals 9841998470
    101 DareDevils 9984725487
    101 DareDevils 9984725487

    Both are same as like.

    can u pls help me

  91. Dear Purushot

    Suppose that table Player_details contains fields idx, Name and ID, and the table contains records as follows:
    100 chennaiRoyals 9841998470
    101 DareDevils 9984725487
    101 DareDevils 9984725487
    103 DareDevilx 9984725487
    103 DareDevilx 9984725487
    104 DareDevily 9984725487
    104 DareDevily 9984725487
    104 DareDevily 9984725487

    The we want delete the duplicate record, those are the records with idx=101 and idx=103 and idx=104, so that just 1 record will be kept, we can follow these step:

    1. Please create this query and save as PlayerDetails_ForDeletingDuplicate
    SELECT *
    FROM dbo.Player_details a
    WHERE ((SELECT COUNT(*)
    FROM dbo.Player_details b
    WHERE a.idx = b.idx AND a.name = b.name AND a.id = b.id) > 1)

    2. Please run this query, if we found any record viewed, then go to next step

    3. Edit this query, change the line:
    select *
    with
    delete dbo.Player_details

    4. In the top this query add this line
    set rowcount 1

    5. Execute this query several times until no more records affected

    6. close this query without save

  92. Dear All,
    I have a table which can accept duplicate records, but it should not accept duplicate within 20 seconds.I want to delete duplicate row which has been created back to back in 20 seconds. Is there any way to delete these records? Please let me know if you need more info about this.

    Thanks in advance.
    Cheers,
    Saravanan

  93. Good coding you guys have displayed. I have just added a while loop to eliminate all duplicates.

    –create fruit table and populate it with duplicate fruits

    CREATE TABLE #tblFruit
    (
    ID int identity,
    Fruit varchar(7)
    )
    INSERT INTO #tblFruit
    VALUES(‘Banana’)
    INSERT INTO #tblFruit
    VALUES(‘Banana’)
    INSERT INTO #tblFruit
    VALUES(‘Pear’)
    INSERT INTO #tblFruit
    VALUES(‘Orange’)
    INSERT INTO #tblFruit
    VALUES(‘Orange’)
    INSERT INTO #tblFruit
    VALUES(‘Apple’)
    INSERT INTO #tblFruit
    VALUES(‘Banana’)
    INSERT INTO #tblFruit
    VALUES(‘Apple’)

    –display all the fruits you have, and count how many duplicates you have to see if your code works

    SELECT * FROM #tblFruit
    SELECT COUNT(FRUIT) FROM #tblFruit
    SELECT COUNT(DISTINCT(FRUIT)) FROM #tblFruit

    –loop through your table and delete any duplicates, display your new table w/o duplicates, count if you still have duplicates or not, delete your temp table

    WHILE EXISTS
    (
    SELECT MIN(ID)
    FROM #tblFruit
    GROUP BY FRUIT
    HAVING COUNT(FRUIT) > 1
    )
    BEGIN
    DELETE
    FROM #tblFruit
    WHERE ID IN ( SELECT MIN(ID) FROM #tblFruit
    GROUP BY Fruit
    HAVING COUNT(Fruit) > 1
    )

    SELECT * FROM #tblFruit
    SELECT COUNT(FRUIT) FROM #tblFruit
    SELECT COUNT(DISTINCT(FRUIT)) FROM #tblFruit
    DROP TABLE #tblFruit

    END

  94. Pinal,
    Thanks for the excellent web site. Just wanted to point out a small typo.

    You have group by DuplicateValueColumn2 twice. It should be DuplicateValueColumn3. I know most people would catch it. Just thought you should update the web site solution with correct code. Thanks.

    Ash

  95. Hi ;
    I have two queries which is below. I want to get the records which are excess in Region1.table don’t want display common records also

    1). SELECT * FROM
    Region1.table
    WHERE POST_KNTNR = ’6731′
    AND SLSKB_INIT = ‘NL’
    AND VAKD = ‘DKK’
    AND year(MDTGL_TMSTM) = 2008

    2). SELECT * FROM
    Region2.table
    WHERE POST_KNTNR = ’6731′
    AND SLSKB_INIT = ‘NL’
    AND VAKD = ‘DKK’
    AND year(MDTGL_TMSTM) = 2008

  96. Hi Ravichandra,

    Use the following code. Replace COLUMN_LIST with column names. Let me know if you have any issues.

    SELECT COLUMN_LIST FROM (SELECT DISTINCT ‘U’ AS SETNAME,
    * FROM REGION1.TABLE WHERE POST_KNTNR = ’6731′
    AND SLSKB_INIT = ‘NL’
    AND VAKD = ‘DKK’
    AND YEAR (MDTGL_TMSTM) = 2008
    UNION ALL
    SELECT DISTINCT NULL, * FROM
    REGION2.TABLE
    WHERE POST_KNTNR = ’6731′
    AND SLSKB_INIT = ‘NL’
    AND VAKD = ‘DKK’
    AND YEAR (MDTGL_TMSTM) = 2008) A
    GROUP BY COLUMN_LIST
    HAVING COUNT (*) = 1 AND MAX (SETNAME) = ‘U’

  97. To delete the DuplicateRows when the ID column is UniqueIdentifier

    DELETE
    FROM MyTable where Id not in(
    SELECT Top 1 b.Id
    FROM MyTable as b
    WHERE b.Col1= MyTable.Col1
    AND b.Col2= MyTable.Col2
    AND b.Col3= MyTable.Col3
    )

  98. Hi,
    Do the following steps:
    1: Create a temp table with the same column names as the main table
    2: Copy the duplicate records by using count>1 in the where clause
    3: Delete the duplicate records in the main table using the count>1
    4: Insert the records from the temp table to the original table
    5: Clear the temp table

  99. If in the sql server table there is no primary key in the table and the data is huge and we want to delete the duplicate records from that data by using the query so tell me which is the feasible way for deleting duplicate records.

  100. Please help with the following:

    SELECT
    date,
    name,
    desc,
    ‘Code’ as cdDesc
    FROM activity
    WHERE
    date = ’2009-01-05′ and
    site = ’80′ and
    exists
    (select code
    from activity
    where code in (’55544′,’33333′, ’66666′)
    GROUP BY code HAVING COUNT(*) > 2)

    UNION ALL

    SELECT
    date,
    name,
    desc,
    ‘Code2′ as cdDesc
    FROM activity
    WHERE
    date = ’2009-01-05′ and
    site = ’80′ and
    exists
    (select code
    from activity
    where code in (’2222′,’3333′, ’66666′, ’88888′)
    GROUP BY code HAVING COUNT (*) > 3)

    The issue is that some code(s) exist in multiple rows and I only need the whole group to display. I used “AND’ but that gave me no results. ANY HELP would be appreciated.

  101. CREATE TABLE #temp
    (SAP_FL varchar(40),
    EQUIPMENT_NO varchar(18),
    SHORT_DESCR varchar(100),
    COMPL_DATE datetime,
    NOTIFICATION_NO varchar(12),
    ACTIVITY_CODE VARCHAR(10),
    ACTIVITY_NAME VARCHAR(100),
    TOTAL_COUNT float,
    CONST_TYPE varchar(100),
    UNIT varchar(50),
    PriorityID int,
    )

    ————————————————————-
    insert into #temp values ( ’1023-A615400-443401-00901-00401′ ,50001564 , ‘Ultrasonic stack #30 ‘ , ’11/14/2005′ , ’500722482′ , ‘REPL’ , ‘Replaced’ , 70628 , 80159257 , ‘EA’ , 2 )
    insert into #temp values ( ’1023-A614400-443401-00901-00501′ ,50001564 , ‘Ultrasonic stack #30 ‘ , ’4/15/2005′ , ’500357639′ , ‘REPL’ , ‘Replaced’ , 70404 , 80159257 , ‘EA’ , 3 )
    insert into #temp values ( ’1023-A615400-443401-00901-00201′ ,50001564 , ‘Ultrasonic stack #30 ‘ , ’3/21/2005′ , ’500329094′ , ‘REPL’ , ‘Repaired’ , 70321 , 80159257 , ‘EA’ , 2 )

    insert into #temp values ( ’1023-F306400-411401-00801′ ,’50001721′, ‘Module- Cutting Fluff Ovrnite/wing 295mm’, ’10/4/2008′ ,’502737991′,’REPR’, ‘Repaired’, 218242 ,’80296971′ ,’EA’, 3)
    insert into #temp values ( ’1023-F306400-411401-00801′ ,’50001721′, ‘Module- Cutting Fluff Ovrnite/wing 295mm’, ’8/29/2008′ ,’502675451′,’REPR’, ‘Repaired’, 218242 ,’80296971′ ,’EA’, 3)
    insert into #temp values ( ’1023-F306400-411401-00801′ ,’50001721′, ‘Module- Cutting Fluff Ovrnite/wing 295mm’, ’8/29/2008′ ,’502620150′,’REPR’, ‘Repaired’, 218242 ,’80296971′ ,’EA’, 3)
    insert into #temp values ( ’1023-F306400-411401-00801′ ,’50001721′, ‘Module- Cutting Fluff Ovrnite/wing 295mm’, ’6/18/2008′ ,’502495333′,’REPR’, ‘Repaired’, 208032 ,’80296971′ ,’EA’, 3)
    insert into #temp values ( ’1023-F306400-411401-00801′ ,’50001721′, ‘Module- Cutting Fluff Ovrnite/wing 295mm’, ’9/7/2007′ ,’501944796′,’REPR’, ‘Repaired’, 197999 ,’80296971′ ,’EA’, 3)
    insert into #temp values ( ’1023-F306400-411401-00801′ ,’50001721′, ‘Module- Cutting Fluff Ovrnite/wing 295mm’, ’9/23/2005′ ,’500648350′,’REPR’, ‘Repaired’, 159277 ,’80296971′ ,’EA’, 3)
    insert into #temp values ( ’1023-F306400-411401-00801′ ,’50001721′, ‘Module- Cutting Fluff Ovrnite/wing 295mm’, ’12/17/2004′,’500182407′,’REPR’, ‘Repaired’, 118935 ,’80296971′ ,’EA’, 3)

    —————————————————-

    select
    t1.SAP_FL as SAP_FL_ST ,
    t2.SAP_FL as SAP_FL_ED,

    t1.EQUIPMENT_NO as EQUIPMENT_NO_ST,
    t2.EQUIPMENT_NO as EQUIPMENT_NO_ED,

    t1.COMPL_DATE as COMPL_DATE_ST ,
    t2.COMPL_DATE as COMPL_DATE_ED,

    t1.SHORT_DESCR as SHORT_DESCR_ST,
    t2.SHORT_DESCR as SHORT_DESCR_ED,

    t1.NOTIFICATION_NO as NOTIFICATION_NO_ST,
    t2.NOTIFICATION_NO as NOTIFICATION_NO_ED,

    t1.ACTIVITY_CODE as ACTIVITY_CODE_ST,
    t2.ACTIVITY_CODE as ACTIVITY_CODE_ED ,

    t1.ACTIVITY_NAME as ACTIVITY_NAME_ST ,
    t2.ACTIVITY_NAME as ACTIVITY_CODE_ED,

    t1.TOTAL_COUNT as TOTAL_COUNT_ST ,
    t2.TOTAL_COUNT as TOTAL_COUNT_ED ,

    t1.TOTAL_COUNT -
    t2.TOTAL_COUNT as TOTAL_COUNT_DIFFERENCE,

    t1.CONST_TYPE as CONST_TYPE_ST ,
    t2.CONST_TYPE as CONST_TYPE_ED ,
    t1.UNIT

    from (select SAP_FL as ‘SAP_FL’,
    EQUIPMENT_NO as ‘EQUIPMENT_NO’,
    SHORT_DESCR as ‘SHORT_DESCR’,
    COMPL_DATE as ‘COMPL_DATE’,
    NOTIFICATION_NO as ‘NOTIFICATION_NO’,
    ACTIVITY_CODE as ‘ACTIVITY_CODE’,
    ACTIVITY_NAME as ‘ACTIVITY_NAME’,
    CONST_TYPE as ‘CONST_TYPE’,
    UNIT as ‘UNIT’
    ,max(TOTAL_COUNT) as ‘TOTAL_COUNT’
    from #temp
    – where EQUIPMENT_NO = 50001721
    group by SAP_FL ,
    EQUIPMENT_NO ,
    COMPL_DATE ,
    NOTIFICATION_NO ,
    ACTIVITY_CODE ,
    ACTIVITY_NAME ,
    SHORT_DESCR ,
    CONST_TYPE ,
    UNIT )t1
    INNER JOIN #temp t2
    ON t1.EQUIPMENT_NO=t2.EQUIPMENT_NO
    AND t1.ACTIVITY_CODE=t2.ACTIVITY_CODE
    AND t1.COMPL_DATE>t2.COMPL_DATE

    =====================================
    OUTPUT–if we considered equi no 50001721
    ==================================
    SAP_FL_ST SAP_FL_ED EQUIPMENT_NO_ST EQUIPMENT_NO_ED COMPL_DATE_ST COMPL_DATE_ED NOTIFICATION_NO_ST NOTIFICATION_NO_ED ACTIVITY_CODE_ST ACTIVITY_CODE_ED ACTIVITY_NAME_ST ACTIVITY_NAME_ED TOTAL_COUNT_ED TOTAL_COUNT_ST TOTAL_COUNT_DIFFERENCE SHORT_DESCR_ST SHORT_DESCR_ED CONST_TYPE_ST CONST_TYPE_ED UNIT
    1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 10/4/2008 8/29/2008 502737991 502675451 REPR REPR Repaired Repaired 218242 218242 0 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
    1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 10/4/2008 8/29/2008 502737991 502620150 REPR REPR Repaired Repaired 218242 218242 0 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
    1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 8/29/2008 502675451 502620150 REPR REPR Repaired Repaired 218242 218242 0 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
    1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 10/4/2008 6/18/2008 502737991 502495333 REPR REPR Repaired Repaired 218242 208032 10210 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
    1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 6/18/2008 502620150 502495333 REPR REPR Repaired Repaired 218242 208032 10210 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
    1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 6/18/2008 502675451 502495333 REPR REPR Repaired Repaired 218242 208032 10210 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
    1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 10/4/2008 9/7/2007 502737991 501944796 REPR REPR Repaired Repaired 218242 197999 20243 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
    1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 6/18/2008 9/7/2007 502495333 501944796 REPR REPR Repaired Repaired 208032 197999 10033 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
    1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 9/7/2007 502620150 501944796 REPR REPR Repaired Repaired 218242 197999 20243 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
    1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 9/7/2007 502675451 501944796 REPR REPR Repaired Repaired 218242 197999 20243 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
    1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 10/4/2008 9/23/2005 502737991 500648350 REPR REPR Repaired Repaired 218242 159277 58965 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
    1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 6/18/2008 9/23/2005 502495333 500648350 REPR REPR Repaired Repaired 208032 159277 48755 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
    1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 9/23/2005 502620150 500648350 REPR REPR Repaired Repaired 218242 159277 58965 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
    1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 9/23/2005 502675451 500648350 REPR REPR Repaired Repaired 218242 159277 58965 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
    1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 9/7/2007 9/23/2005 501944796 500648350 REPR REPR Repaired Repaired 197999 159277 38722 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
    1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 10/4/2008 12/17/2004 502737991 500182407 REPR REPR Repaired Repaired 218242 118935 99307 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
    1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 6/18/2008 12/17/2004 502495333 500182407 REPR REPR Repaired Repaired 208032 118935 89097 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
    1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 12/17/2004 502620150 500182407 REPR REPR Repaired Repaired 218242 118935 99307 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
    1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 12/17/2004 502675451 500182407 REPR REPR Repaired Repaired 218242 118935 99307 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
    1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 9/23/2005 12/17/2004 500648350 500182407 REPR REPR Repaired Repaired 159277 118935 40342 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
    1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 9/7/2007 12/17/2004 501944796 500182407 REPR REPR Repaired Repaired 197999 118935 79064 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
    1023-A614400-443401-00901-00501 1023-A615400-443401-00901-00201 50001564 50001564 3/21/2005 4/15/2005 500357639 500329094 REPL REPL Replaced Repaired 70404 70321 83 Ultrasonic stack #30 Ultrasonic stack #30 80159257 80159257 EA
    1023-A615400-443401-00901-00401 1023-A614400-443401-00901-00501 50001564 50001564 4/15/2005 11/14/2005 500722482 500357639 REPL REPL Replaced Replaced 70628 70404 224 Ultrasonic stack #30 Ultrasonic stack #30 80159257 80159257 EA
    1023-A615400-443401-00901-00401 1023-A615400-443401-00901-00201 50001564 50001564 3/21/2005 11/14/2005 500722482 500329094 REPL REPL Replaced Repaired 70628 70321 307 Ultrasonic stack #30 Ultrasonic stack #30 80159257 80159257 EA

    here in output it puts tries to take start date as max date and calcuates difference based on max(date)- all remaining dates

    but i want in this sequence

    1023-F306400-411401-00801 50001721 Module, Cutting Fluff Ovrnite/wing 295mm 10/4/2008 502737991 REPR Repaired 218242 80296971 EA 3
    1023-F306400-411401-00801 50001721 Module, Cutting Fluff Ovrnite/wing 295mm 8/29/2008 502675451 REPR Repaired 218242 80296971 EA 3
    1023-F306400-411401-00801 50001721 Module, Cutting Fluff Ovrnite/wing 295mm 8/29/2008 502620150 REPR Repaired 218242 80296971 EA 3
    1023-F306400-411401-00801 50001721 Module, Cutting Fluff Ovrnite/wing 295mm 6/18/2008 502495333 REPR Repaired 208032 80296971 EA 3
    1023-F306400-411401-00801 50001721 Module, Cutting Fluff Ovrnite/wing 295mm 9/7/2007 501944796 REPR Repaired 197999 80296971 EA 3
    1023-F306400-411401-00801 50001721 Module, Cutting Fluff Ovrnite/wing 295mm 9/23/2005 500648350 REPR Repaired 159277 80296971 EA 3
    1023-F306400-411401-00801 50001721 Module, Cutting Fluff Ovrnite/wing 295mm 12/17/2004 500182407 REPR Repaired 118935 80296971 EA 3
    1023-A614400-443401-00901-00501 1023-A615400-443401-00901-00201 50001564 50001564 3/21/2005 4/15/2005 500357639 500329094 REPL REPL Replaced Repaired 70404 70321 83 Ultrasonic stack #30 Ultrasonic stack #30 80159257 80159257 EA
    1023-A615400-443401-00901-00401 1023-A614400-443401-00901-00501 50001564 50001564 4/15/2005 11/14/2005 500722482 500357639 REPL REPL Replaced Replaced 70628 70404 224 Ultrasonic stack #30 Ultrasonic stack #30 80159257 80159257 EA
    —————————————————————————
    can anybody has the solution for this?

  102. @qute/cute

    Dude, Your query is incomplete, I spent nearly an hour working on this, But I could not understand what you want in the select statement.

    You say you are trying to calculate difference, where is the difference, you have 4 columns you call them Total_Count, you are not even using any function to calculate difference, query is not clear at all and also the sample data… I mean atleast give me the select query properly, data ( insert statements )you provided are not correct comparing to the out put what you have given in your post.

    Please post your complete questions.

    Regards,
    IM.

  103. @dyamond

    When ever you use, Group BY, you need to have a aggregate function in your select list. The subquery you are trying to use has group by but no Aggregate function, also you are saying having Count(*) > 2 at one place and having Count(*) > 3 at another place, but where exactly is this count(*), are you writing this any place.

    SELECT
    [date],
    [name],
    [desc],
    ‘Code’ as cdDesc
    FROM activity
    WHERE date = ’2009-01-05′
    and site = ’80′
    and exists
    (
    select code , count(*) Counts
    from activity
    where code in (’55544′,’33333′, ’66666′)
    GROUP BY code HAVING COUNT(*) > 2
    )

    UNION ALL

    SELECT
    [date],
    [name],
    [desc],
    ‘Code2′ as cdDesc
    FROM activity
    WHERE date = ’2009-01-05′
    and site = ’80′
    and exists
    (
    select code, count(*) Counts
    from activity
    where code in (’2222′,’3333′, ’66666′, ’88888′)
    GROUP BY code HAVING COUNT (*) > 3
    )

    Try using the code like above.

    I did not test the script functionality, since I do not have sample dat, If script still does not work then please post table structure script and some sample data and expected output.

    So that we can help you out.

    Regards,
    IM.

  104. /*
    Input Param:
    @vchStartDate [in yyyymmdd format]
    @intDuration [specifying the no. of days for end date calculation]
    This code block will calculate the end date based on the supplied start date and duration.
    Finally it will return the dates and the day starting from start date to end date both inclusive.
    Ist Resultset- Returns All Dates in the range starting from start date to end date.
    IInd Resultset- Returns All Dates in the range starting from start date to end date excluding saturdays and sundays.
    IIIrd Resultset- Returns All Dates in the range starting from start date to end date excluding weekdays.
    */
    DECLARE @vchStartDate VARCHAR(10)
    DECLARE @intDuration INT
    DECLARE @LclvchEndDate VARCHAR(10)

    /* Set input values */
    SET @vchStartDate = ’20090101′
    SET @intDuration = 50

    /* Calculate End Date */
    SET @LclvchEndDate = CONVERT(VARCHAR(10),DATEADD(dd,@intDuration, CAST(@vchStartDate AS DATETIME)),112);
    WITH mycte AS
    (
    SELECT CAST(@vchStartDate AS DATETIME) DateValue
    UNION ALL
    SELECT DateValue + 1
    FROM mycte
    WHERE DateValue + 1 <= @LclvchEndDate
    )

    SELECT DateValue
    INTO #tbl_data
    FROM mycte
    OPTION (MAXRECURSION 0)

    /* Ist Resultset- Returns All Dates in the range starting from start date to end date. */
    SELECT CONVERT(VARCHAR(10),DateValue,121) Date,
    [Day] =
    CASE
    WHEN DATEPART(dw,DateValue)= 1 THEN ‘SUNDAY’
    WHEN DATEPART(dw,DateValue)= 2 THEN ‘MONDAY’
    WHEN DATEPART(dw,DateValue)= 3 THEN ‘TUESDAY’
    WHEN DATEPART(dw,DateValue)= 4 THEN ‘WEDNESDAY’
    WHEN DATEPART(dw,DateValue)= 5 THEN ‘THURSDAY’
    WHEN DATEPART(dw,DateValue)= 6 THEN ‘FRIDAY’
    WHEN DATEPART(dw,DateValue)= 7 THEN ‘SATURDAY’
    END
    FROM #tbl_data

    /* IInd Resultset- Returns All Dates in the range starting from start date to end date excluding saturdays and sundays. */
    SELECT CONVERT(VARCHAR(10),DateValue,121) Date,
    [Day] =
    CASE
    WHEN DATEPART(dw,DateValue)= 2 THEN ‘MONDAY’
    WHEN DATEPART(dw,DateValue)= 3 THEN ‘TUESDAY’
    WHEN DATEPART(dw,DateValue)= 4 THEN ‘WEDNESDAY’
    WHEN DATEPART(dw,DateValue)= 5 THEN ‘THURSDAY’
    WHEN DATEPART(dw,DateValue)= 6 THEN ‘FRIDAY’
    END
    FROM #tbl_data
    WHERE DATEPART(dw,DateValue) NOT IN (1,7)

    /* IIIrd Resultset- Returns All Dates in the range starting from start date to end date excluding weekdays. */
    SELECT CONVERT(VARCHAR(10),DateValue,121) Date,
    [Day] =
    CASE
    WHEN DATEPART(dw,DateValue)= 1 THEN ‘SUNDAY’
    WHEN DATEPART(dw,DateValue)= 7 THEN ‘SATURDAY’
    END
    FROM #tbl_data
    WHERE DATEPART(dw,DateValue) IN (1,7)

    DROP TABLE #tbl_data

  105. Hi pinal,

    your quary is good ut it has some limitation like identity col.

    i have a better idea to do delete duplicate record using CTE.

    like:

    CREATE TABLE [dbo].[testing3](
    [id] [int] NULL,
    [name] [varchar](50)
    ) ON [PRIMARY]

    with dup as(select *,row_number() over(partition by id order by id)as TID from testing3)

    delete from dup where tid >1

  106. Execute these queries for learning how to remove duplicate record.
    CREATE TABLE dbo.Test1 (
    [ID] [int] ,
    [FirstName] [varchar](25),
    [LastName] [varchar](25)
    ) ON [PRIMARY]

    INSERT INTO Test1 VALUES(1, ‘Bob’,’Smith’)
    INSERT INTO Test1 VALUES(2, ‘Dave’,’Jones’)
    INSERT INTO Test1 VALUES(3, ‘Karen’,’White’)
    INSERT INTO Test1 VALUES(1, ‘Bob’,’Smith’)
    INSERT INTO Test1 VALUES(4, ‘Bobby’,’Smita’)

    select identity(int,1,1) as SlNo,* into #temp from Test1

    DELETE
    FROM #temp
    WHERE SlNo NOT IN
    (
    SELECT MAX(SlNo)
    FROM #temp
    GROUP BY ID,FirstName,lastname
    )

    drop table test1

    select * into test1 from #temp

    alter table test1 drop column SlNo

    select * from test1 order by id

  107. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  108. –Try this to delete multiple records

    ;with t1 as
    (
    select id,Row_number() over (partition by a, b, c order by a, b, c) as rnum
    from mytable with(nolock)
    )
    delete from t1 where rnum>1

  109. Pingback: SQL SERVER - Find Nth Highest Salary of Employee - Query to Retrieve the Nth Maximum value Journey to SQL Authority with Pinal Dave

  110. hi guys
    i have a problem ??? what if the entire row is duplicate ??
    with i dentity column also same .

    i am transfering data from a table having duplicate rows to a table having a primary key

    pls help me out

  111. gaurav,
    Praney’s sql in the comments will help you out. I have the same situation and that solved the problem.

    ;with t1 as
    (
    select id,Row_number() over (partition by a, b, c order by a, b, c) as rnum
    from mytable with(nolock)
    )
    delete from t1 where rnum>1

  112. Hi Gaurav,
    I have duplicate records in a table,till recently i added a
    datetime field in my table called timestamp.Now when
    duplicates go into this table i will be able to see the current
    date in the timestamp

    Firstname Lastname memberno timestamp
    Jack Hill 11111 2009-12-11
    Jack Hill 11111 null
    Simon Philip 222222 2009-12-11
    Simon Philip 222222 2009-12-12
    Sam Wheat 33333 nulll
    Sam Wheat 33333 null

    In this table i want to fetch a duplicate set which has
    one record with a valid timestamp and other timestamp
    as null – something like

    Firstname Lastname memberno timestamp
    Jack Hill 11111 2009-12-11
    Jack Hill 11111 null

    select max(timestamp) from member group by firstname,lastname having count(*)>1

    How do i modify my display duplicate statement to have the resultset which includes duplicates with and without
    a valid timestamp

  113. Hi Pinal

    With reference to the above question
    Can anyone help me out with a query which can display only duplicates with or witout a timestamp, they should
    not include duplicates members all having timestamps

  114. Hi Luke,

    You can use ROW_Number() to identify Duplicate Rows

    Here You need to do as:

    ;with CTE AS(
    select Row_NUmber() (OVER PARTITION BY FirstName, LastName, MemberNo Order BY TimeStamp ASC) AS RowID
    FROM table
    )
    select * from cte where rowID=1

    this will give you Records with NULL. If you want data with Dates then you need to change “ORDER BY” to “Order BY TimeStamp DESC”

    For further details, you can read it on my blog: http://tejasnshah.wordpress.com/2008/12/31/find-duplicate-rows-with-row_number-sql-server-2005/

    Thanks,

    Tejas

  115. Hi Pinal,

    Your articles have always helped me. Just curious if I could rationalize data in the example below using a variant of this example (I have an option to use SSIS Fuzzy lookup, but its way off my application scope. So it is ruled out.)

    Cheers,
    Krish

  116. HI PINAL,

    I HAVE READ YOUR ARTICLES AND RESPONSES FOR THE QUERIES AND I FOUND IT GOOD.
    I TOO HAVE ONE QUERY.

    THERE IS TABLE1 AND TABLE2
    TABLE 1 CONTAINS 100 RECORDS.
    TABLE 2 CONTAINS 150 RECORDS.

    TABLE 2 CONTAINS SOME RECORDS WHICH ARE ALREADY PRESENT IN TABLE 1.

    NOW WHEN I MERGE BOTH IT ADDS ALL THE RECORDS GIVING TOTAL NO. TO 250.

    FROM THESE I WANT TO REMOVE THE DUPLICATE DATA WHICH I GOT FROM TABLE 2 AND ARE ALREADY PRESET IN TABLE 1.

    I have tried above methods but did not yield required results.

    Be Helpful…

  117. I got the solution of above problem

    FRIEND in a NEED

    Another Question.

    i am joining TABLE1 (older) And TABLE2 (newer).

    i found some records which are common in both table.

    i want to remove found common data from TABLE2(newer)

    How can i perform that operation?

    GENERAL QUESTION
    How can we perform the Update,Delete operation on query of join

    –common records in two tables using inner join
    select *
    from TABLE1 as e
    inner join TABLE2 as c
    on e.respondentid = c.respondentid

  118. Vikas, for the second question:

    DELETE FROM Table2 WHERE EXISTS(SELECT * FROM Table1 WHERE Table1.respondentid = Table2.respondentid)

  119. i have table TABLE1 with data inside.
    i want to add one column name as “Serial Number”.
    inside this i want to give Automatic Serial No.
    Say there are 5000 records then the Serial Number Column should contain1,2,3,4…..5000 at last record.

    Be Beedful

  120. i got the solution of above query.

    “alter table table1 add ID INT IDENTITY(1,1)”

    But this inserts the column at the end.

    i want this column to be inserted at the start.

    i.e. First Column should be of identity Column

  121. i have a table TABLE1 of Five Column.
    I want to insert one more column in this table after First Column.
    How can i insert the New Column from SQL Editor in between the Columns Present not at the end?

  122. Hi Pinal,

    I HAVE READ YOUR ARTICLES AND RESPONSES FOR THE QUERIES AND I FOUND IT GOOD.
    Actully i m new in SQL SERVER2005.

    I stucked in a query, if find the solution:-

    Table1 Table2

    col1|col2 col1|col2
    10 100 10 100
    11 150 15 200
    15 200 13 210
    21 210 23 100

    Output should be:

    col1|col2
    10 100
    11 150
    21 210
    13 210
    23 100

  123. Hi Pinal,

    i have one more query:

    Delete the duplicates rows from a table if there is not any primary key.

    Please find the solution and Thanks in advance.

  124. To aLL,

    I have one table TABLE! , which contains one column named “EMPLOYEE NAME”.
    There are various values inside it.
    i want to check every field of EMPLOYEE NAME and find out if there is any Numeric Value inside.

    EG.
    EMPLOYEE NAME
    Billgates
    George
    A1dam –Here 1 is present in between
    Gem9ini –Here 9 is present in between

    i want to identify this types of name which contains the numeric value in the field.

  125. using the instructions that you posted about adding an id column:

    “Good Question. I have been asking this question in interview many times to new candidates. Answer is:
    1) Add Identity Col and perform the operation displayed in this blog and drop Identity Col.
    2) User Cursor and save your values in temp table. If you receive the same row which you put in temp table delete it.
    3) You can use while loop as well do the same as cursor”

    i get the error message from sql telling me it cannot insert the value NULL into column “id” column does not allow nulls (i have got this after trying to set the primary key on the id field)

  126. @Domenic

    I don’t think, I understood your question completely,

    I think you asked, why do you get this error:
    error message from sql telling me it cannot insert the value NULL into column “id” column does not allow nulls (i have got this after trying to set the primary key on the id field)

    Try this,
    – Step 1: Create a table
    CREATE TABLE example1 ( id INT NOT NULL , ename VARCHAR(10))

    – Step 2 : Insert some data in this table, few duplicate records
    INSERT INTO example1 VALUES( 1, ‘imran’)
    INSERT INTO example1 VALUES( 1, ‘imran’)
    INSERT INTO example1 VALUES( 2, ‘imran’)
    INSERT INTO example1 VALUES( 2, ‘imran’)
    INSERT INTO example1 VALUES( 3, ‘imran’)

    – Step 3: lets add Identity Column
    ALTER TABLE example1
    ADD Iden INT IDENTITY

    – Step 4: Lets remove duplicate records.
    DELETE
    FROM example1
    WHERE Iden NOT IN
    (
    SELECT MAX(Iden)
    FROM example1
    GROUP BY id, ename)

    – Check if duplicate records still exists ?
    SELECT * FROM example1

    – Step 6: drop Identity column, which we added earlier.
    ALTER TABLE example1
    DROP COLUMN Iden

    – Step 7: Lets make id column Primary key.
    ALTER TABLE example1
    ADD CONSTRAINT PK_Example1 PRIMARY KEY (id)

    This works well with no issues.

    if you replace step 1: with below script
    – step1 (a) : Create a table

    CREATE TABLE example1 ( id INT , ename VARCHAR(10))

    if you repeat from step 2 – Step 7, step7 will fail, because id column in example1 table allows Null as you can see, I did not create that column with NOT NULL constraint, that is why you cannot make that column as primary key.

    Does this answer your question ? If not please be clear what you actually want to ask ?

    ~ IM.

  127. Table1 Table2

    ID Sim_No Sim_No Plate
    1 2203080 2203080 AD2345
    2 2203081
    3 2203082

    How I delete The Sim No From Table1(Same Record In Table2 ) ( If I Take any No From Table1 & put In To Table 2)

  128. @Jerry

    Whatever routine is used to INSERT the data INTO Table2, can also DELETE from Table1.

    Or, for a general solution (which may not be very quick) DELETE any records in Table1 that EXIST in Table2:

    DELETE FROM Table1 WHERE EXISTS (SELECT * FROM Table2 WHERE Table2.Sim_No = Table1.Sim_No)

  129. ItemTable

    ItemNo BatchNo Qty
    11 101 100
    11 102 500
    12 101 100
    12 101 500
    13 105 1000
    11 101 600

    Now I want like this
    ItemNo BatchNo Qty
    11 101 700
    11 102 500
    12 101 600
    13 105 1000

    how to write query for this
    the above formate has Sample record in our project it have 35000 record

  130. @tamilselvan

    Do you want a regular GROUP BY?

    SELECT ItemNo, BatchNo, SUM(Qty)
    GROUP BY ItemNo, BatchNo;

  131. create table example1 (ItemNo int ,BatchNo int ,Qty int)

    insert into example1 values (11 ,101, 100)
    insert into example1 values (11, 102, 500)
    insert into example1 values (12, 101, 100)
    insert into example1 values (12, 101, 500)
    insert into example1 values (13, 105, 1000)
    insert into example1 values (11, 101, 600)

    select ItemNo
    ,BatchNo
    ,Sum(Qty) Qty
    from example1
    group by ItemNo
    ,BatchNo

  132. I have a few records that are duplicates, all I need to do is mark them as duplicates.

    select field01 from table01
    field01
    AA
    AB
    AC
    AB
    AA
    AD

    All that I require is
    select field01, mark01 from table01 to return
    field01 ; mark01
    AA; 1
    AB; 1
    AC; 1
    AB; 2
    AA; 2
    AD; 1

  133. @gpshiburaj

    ROW_NUMBER() does that:

    WITH
    Data(field01)
    AS
    (
    SELECT ‘AA’ UNION ALL
    SELECT ‘AB’ UNION ALL
    SELECT ‘AC’ UNION ALL
    SELECT ‘AB’ UNION ALL
    SELECT ‘AA’ UNION ALL
    SELECT ‘AD’
    )
    SELECT
    field01,
    ROW_NUMBER() OVER(PARTITION BY field01 ORDER BY field01)
    FROM
    Data;

  134. Hello Pinal,

    Is there any procedure to restore deleted records from particular table in database?

    Thank you
    Amit Jain

  135. Hey Guys thats all fine . U can store the values in the Temp table remove the duplicate records and empty the original table . Then move all the unique records to the original table . But its a lengthy process suppose i am having 2 million records in a table and from that table if i want to ren=move the duplicate records then it would really affect the performance . Sorry Since i am not having so much of exp and if i had hurted someone . Pinal Sir do let me know if i am wrong

  136. TABLE- MASTER,W_CODE IS COLUMN NAME.

    DELETE FROM MASTER WHERE W_ID IN(SELECT MAX(W_ID) FROM MASTER WHERE W_CODE IN(SELECT W_CODE FROM MASTER GROUP BY W_CODE HAVING COUNT(W_CODE)>1))

  137. Hello Pinal,

    Could you please help me? ..When I ran my script it removed both of the records. Could you please advice me why?
    I do not have index in that table.

    Thank you!

    drop table dupcalref;
    create temporary table dupcalref
    SELECT MAX(calownerid) as id FROM calref
    group by calownerid, calenderid having count(calownerid)>1;

    alter table dupcalref add index(id);

    delete FROM calref where calownerid in
    (select id from dupcalref);

  138. You could use CTE to delete the duplicates if you have SQL 2005 or above.

    Create the duplicate table: ———————————–
    IF OBJECT_ID(‘SalesHistory’) IS NOT NULLDROP TABLE SalesHistoryCREATE TABLE [dbo].[SalesHistory] ( [Product] [varchar](10) NULL, [SaleDate] [datetime] NULL, [SalePrice] [money] NULL ) GOINSERT INTO SalesHistory(Product, SaleDate, SalePrice)SELECT ‘Computer’,’1919-03-18 00:00:00.000′,1008.00UNION ALLSELECT ‘BigScreen’,’1927-03-18 00:00:00.000′,91.00UNION ALLSELECT ‘PoolTable’,’1927-04-01 00:00:00.000′,139.00UNION ALLSELECT ‘Computer’,’1919-03-18 00:00:00.000′,1008.00UNION ALLSELECT ‘BigScreen’,’1927-03-25 00:00:00.000′,92.00UNION ALLSELECT ‘PoolTable’,’1927-03-25 00:00:00.000′,108.00UNION ALLSELECT ‘Computer’,’1919-04-01 00:00:00.000′,150.00UNION ALLSELECT ‘BigScreen’,’1927-04-01 00:00:00.000′, 123.00UNION ALLSELECT ‘PoolTable’,’1927-04-01 00:00:00.000′, 139.00UNION ALLSELECT ‘Computer’,’1919-04-08 00:00:00.000′, 168.00
    =======================================

    Remove the duplicates —————————————

    ;WITH SalesCTE(Product, SaleDate, SalePrice, Ranking)AS(SELECT Product, SaleDate, SalePrice,Ranking = DENSE_RANK() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC)FROM SalesHistory)DELETE FROM SalesCTEWHERE Ranking > 1

  139. Pinal,

    Thanks a ton, I spent a day experimenting with how to do this before I came across your solution, I added a key to my table and this worked like a charm.

  140. I want to delete the duplicate record ( i.e i have 10 duplicate record , i want to delete a particular record leaving all the remaining 9 records means , how can i doit , plz reply me )

    • please clarify your question which particular record u want to delete from 10 record. then i will give u answer.
      if u want delete only single record out of ten record then fallow this: ( with respect to oracle database)

      1)
      delete from table_name
      where Rowid in (select max (Rowid) from from table_name
      group by all_duplicate_column;

      (e.g here table_name: Emp , all_duplicate_column: Emp_id)

      note: u can use min() fun at max place result ud be same

      2)
      delete from table_name x
      where Rowid in (select max (Rowid) from from table_name y
      where x.rowid = y.rowid);

      Note: both quary delete only 1 record and return 9 dublicate record

  141. HI Manoj, you can do so by setting row count 1 and put the delete statement, this way you will delete only 1 record of the 10 duplicate records. Avineet

  142. Hi Sir
    i use Microsoft access 2003
    how to make the same record not to calculate the total or can i count the same record and devided by the count so that i can get original sum account
    because duplicate record sum it all up
    which i only want to have only one record

  143. Hi Pinal
    How are you , You have all the good articale
    My question is that
    I have table look like
    ID Name
    1 Deepak
    1 Deepak
    1 Deepak
    1 Deepak
    1 Deepak

    how to write a single query
    Remain one Row eccept All row has been Deleted

  144. You can use the following query which has been published in the above posts:-

    CREATE TABLE #Table3 (col1 int, col2 int)
    INSERT INTO #Table3 VALUES (1, 1000)
    INSERT INTO #Table3 VALUES (1, 1000)
    INSERT INTO #Table3 VALUES (1, 1000)
    INSERT INTO #Table3 VALUES (1, 1000)
    INSERT INTO #Table3 VALUES (1, 1000)
    INSERT INTO #Table3 VALUES (1, 1000)

    SELECT * FROM #Table3;

    WITH T1 AS (SELECT (ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col1)) AS RNum FROM #Table3)
    DELETE FROM T1 WHERE RNum IN (SELECT a.RNum FROM T1 AS a, T1 AS b WHERE a.RNum > b.RNum GROUP BY a.RNum)

    SELECT * FROM #Table3

  145. Hi I want to get duplicate records from the table but condition is,
    suppose there r 3 columns A,B,C & in that
    A column has data like…kiran is working.. & in B column data is …ramesh is working…..

    I want to delete particular record i.e delete only common data like ” is working” from both A & B column…

    Any body help me the same….

    Thanks in Advance….

    kiran…

  146. hi,

    Pinal can u please tell me about to getting olny duplicate records.
    iam able to get duplicate decords from my table and but there was an unique identifier so we are getting only on record the duplicate record will not be get retrived

  147. hi
    i applied query in my database as

    delete from table where id not in (select max(id) from table group by duplicate column name………)

    but its not working in sql server 2000 .

    this query is only useful for oracle where we can replace id as row id .

    so please help me if any one have ans of this question?

    Deep

  148. DELETE
    FROM MyTable
    WHERE ID NOT IN
    (
    SELECT MAX(ID)
    FROM MyTable
    GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn2)

    This qyery is not working can any one tell me why we are using id inthis qury and in oracle it is rowid. so please give me same query to resolve the issue.

  149. Hi,

    I have 2 tables. I would like to merge the two tables and remove any duplicates of email address.
    Table 1: Purchaser (Coy id, coy name & email add)
    Table 2: Member Table (Coy id, coy name & email add)

    Tried using the UNION sql doesnt work and group by as well.

  150. Hi,

    WE can get the distinct from the union of these two tables as below:

    SELECT DISTINCT * FROM
    (SELECT * FROM Purchaser
    UNION
    SELECT * FROM Member)

    Another new method in SQL Server 2008 is using MERGE clause.

    Regards,
    Pinal Dave

  151. Hi Pinal,

    Thanks for the fast respond :)
    Tried the one you advise as well. Still unsuccessful
    —————————————————————————
    SELECT DISTINCT * FROM

    (SELECT [Test_Acctron Mbr Email].company_id,[Test_Acctron Mbr Email].company_name, [Test_Acctron Mbr Email].contact_email1
    FROM [Test_Acctron Mbr Email]

    UNION

    SELECT [Publication Purchasers List].CoyID, [Publication Purchasers List].CoyName ,[Publication Purchasers List].Email1
    FROM [Publication Purchasers List])
    ——————————————————————

    My Original : – Seems “syntax error in FROM Clause”
    =============================
    SELECT P.CoyID,P.CoyName,P.Email

    FROM [SELECT Max([Test_Acctron Mbr Email].company_id) AS CoyID, Max([Test_Acctron Mbr Email].company_name) AS CoyName, [Test_Acctron Mbr Email].contact_email1 AS Email
    FROM [Test_Acctron Mbr Email]
    UNION
    SELECT Max([Publication Purchasers List].CoyID) AS CoyID, Max([Publication Purchasers List].CoyName) AS CoyName, [Publication Purchasers List.Email] AS Email
    FROM [Publication Purchasers List]]. AS P
    GROUP BY P.email;
    =============================

  152. Hi,

    We are missing the derived table name. So use the below query:

    SELECT DISTINCT * FROM
    (SELECT [Test_Acctron Mbr Email].company_id,[Test_Acctron Mbr Email].company_name, [Test_Acctron Mbr Email].contact_email1
    FROM [Test_Acctron Mbr Email]
    UNION
    SELECT [Publication Purchasers List].CoyID, [Publication Purchasers List].CoyName ,[Publication Purchasers List].Email1
    FROM [Publication Purchasers List]) as tmp

    Regards,
    Pinal Dave

  153. hi,

    The problem is i am having the columns of products_model, products_id(primary key), products_last_modified_date……etc

    Here i have duplicate records of products_model, so i needs to remove old duplicate records and retain the new records based on products_last_modified_date column
    kindly replay me

    Thanks

  154. @Rathin

    Check if this helps…

    – ** WORKS ONLY IN SQL SERVER 2005 **

    declare @Example1 table
    ( Products_id int –constraint PK_Products_id primary key
    ,Products_model varchar(50) NULL
    , Products_last_modified_date datetime NULL)

    – Nothing Loaded
    select * from @Example1

    insert into @Example1 values (1, ‘ABC’, ’1/1/2000′)
    insert into @Example1 values (23, ‘ABC’, ’2/1/2000′)
    insert into @Example1 values (34, ‘ABC’, ’3/1/2000′) — This is Latest Record
    insert into @Example1 values (46, ‘DEF’, ’1/1/2000′)
    insert into @Example1 values (51, ‘DEF’, ’3/1/2000′)– This is Latest Record
    insert into @Example1 values (63, ‘DEF’, ’2/1/2000′)

    – Everything Loaded
    select * from @Example1

    – Deleting duplicates, given condition: dont delete latest record.
    – Logic: Picking Max of dates and comparing max date with all dates for that specific record, if max date is given date that record will be ignored otherwise it will be deleted.

    Delete D From
    (select Products_id
    ,Products_model
    ,Products_last_modified_date
    ,MAX(Products_last_modified_date) OVER (PARTITION BY Products_Model )MAX_DATETIME_STAMP
    from @Example1) D
    Where Products_last_modified_date MAX_DATETIME_STAMP

    – ** IMPORTANT ** —
    – Please add your complete logic to OVER PARTITION BY

    – After Delete
    select * from @Example1

    ~ IM.

  155. hi,

    Sorry i am mentioned that having the problem with Mysql DB

    (The problem is i am having the columns of products_model, products_id(primary key), products_last_modified_date……etc

    Here i have duplicate records of products_model, so i needs to remove old duplicate records and retain the new records based on products_last_modified_date column
    kindly replay me)

    Is it possible , can we solve this by using Group by & having function?

  156. Hi Pinal,

    Thanks for your post. I have another idea.
    It can also be done in this way and it will work even if the Table does not have any identity column.

    Query:

    WITH xTab AS
    (
    SELECT
    DuplicateColumn1,
    DuplicateColumn2,
    ROW_NUMBER() OVER (PARTITION BY DuplicateColumn3 ORDER BY Column3) AS [Count]
    FROM MyTable
    )
    DELETE FROM xTab WHERE [Count] > 1

    • Hello Souvik,

      Thank you for sharing the method of getting duplicate using CTE. I appreciate this method as it is easy and better.
      But the PARTITION BY clause should include all columns of table while you have used only one column.

      Regards,
      Pinal Dave

  157. Hi,

    Please replace the sixth line of the query with this:

    ROW_NUMBER() OVER (PARTITION BY DuplicateColumn3 ORDER BY DuplicateColumn3 )

  158. Hi,

    I have one doubt. In this site for the query for fetching the Nth highest salary, we could see that the subquery contains Order By clause.

    But when I see the properties of a sub query, I could see the statement that “A subquery cannot contain a ORDER-BY clause.”

    Could you please clarify this.

    Regards,
    Jyoti

  159. Deleting all the duplicate rows from a table keeping only the distinct values(try out this).

    create table test_a
    (a int
    )
    insert into test_a values(1)
    insert into test_a values(1)
    insert into test_a values(3)
    insert into test_a values(4)
    insert into test_a values(5)
    insert into test_a values(1)
    insert into test_a values(2)
    insert into test_a values(3)
    insert into test_a values(4)
    insert into test_a values(5)

    WITH [test_a rowid]
    AS (SELECT a.a,
    Row_number()
    OVER(ORDER BY a.a) num
    FROM test_a a)
    DELETE FROM [test_a rowid]
    WHERE num NOT IN (SELECT Rank()
    OVER(ORDER BY b.a)
    FROM test_a b)

  160. Here’s my dynamic dupes removal script. All it requires is the table name, make sure you have your db selected in the query editor dropdown. Just make @tablename=” and this’ll take care of the rest.

    Set ansi_nulls ON if you want the script to ignore null values. If some of your dupes include nulls then leave it set to ansi_nulls off.

    script:
    —————————————————————–
    DECLARE @tablename VARCHAR(255)
    DECLARE @columnnames VARCHAR(MAX)
    DECLARE @maxlength VARCHAR(max)
    DECLARE @datatype VARCHAR(max)
    DECLARE @columnlist VARCHAR(MAX)
    DECLARE @select VARCHAR(MAX)
    DECLARE @rowcount int
    DECLARE @variablelist VARCHAR(MAX)
    DECLARE @currentvariablename VARCHAR(MAX)
    DECLARE @declarestatement VARCHAR(MAX)
    DECLARE @singledeclare VARCHAR(MAX)
    DECLARE @FINALscript VARCHAR(MAX)
    DECLARE @wherestatement VARCHAR(MAX)
    DECLARE @ansinulls VARCHAR(MAX)

    SET @ansinulls = ‘SET ANSI_NULLS OFF’
    SET @tablename=’tSTG_CNT_MVDB_SurveyExtract_Presurvey’

    SET @columnlist= ”
    SET @wherestatement = ”
    SET @rowcount = ”
    SET @maxlength = ‘asdfadf’
    SET @columnnames = ”
    SET @datatype = ”
    SET @select = ”
    SET @variablelist = ”
    SET @currentvariablename = ”
    SET @declarestatement = ”
    SET @singledeclare = ”
    SET @FINALscript = ”

    SET @rowcount = (SELECT COUNT(column_name)
    FROM information_schema.columns
    WHERE table_name =@tablename)

    DECLARE dublicate_cursor1 CURSOR READ_ONLY FOR
    SELECT column_name,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH
    FROM information_schema.columns
    WHERE table_name = @tablename

    OPEN dublicate_cursor1

    FETCH NEXT FROM dublicate_cursor1 INTO @columnnames,@datatype,@maxlength
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @currentvariablename = ‘@’ + @columnnames

    IF @rowcount = 1
    BEGIN

    SET @columnlist = @columnlist + @columnnames
    SET @variablelist = @variablelist + @currentvariablename + ‘,@Count’
    IF @maxlength IS NULL
    Begin
    SET @singledeclare = ‘DECLARE ‘ + @currentvariablename + ‘ ‘ + @datatype
    END
    ELSE
    BEGIN
    SET @singledeclare = ‘DECLARE ‘ + @currentvariablename + ‘ ‘ + @datatype + ‘ (‘ + @maxlength + ‘)’
    END
    SET @declarestatement = @declarestatement + @singledeclare + ‘ DECLARE @Count int’
    SET @wherestatement = @wherestatement + @columnnames + ‘=’ + @currentvariablename
    END
    ELSE
    BEGIN
    –PRINT ‘ELSE’
    SET @columnlist = @columnlist + @columnnames + ‘,’
    SET @variablelist = @variablelist + @currentvariablename + ‘,’
    IF @maxlength IS NULL
    Begin
    SET @singledeclare = ‘DECLARE ‘ + @currentvariablename + ‘ ‘ + @datatype
    END
    ELSE
    BEGIN
    SET @singledeclare = ‘DECLARE ‘ + @currentvariablename + ‘ ‘ + @datatype + ‘ (‘ + @maxlength + ‘)’
    END
    SET @declarestatement = @declarestatement + ‘ ‘ + @singledeclare + ‘ ‘
    SET @wherestatement = @wherestatement + @columnnames + ‘=’ + @currentvariablename + ‘ AND ‘
    END
    SET @rowcount = @rowcount – 1
    FETCH NEXT FROM dublicate_cursor1 INTO @columnnames,@datatype,@maxlength
    END

    CLOSE dublicate_cursor1
    DEALLOCATE dublicate_cursor1

    SET @select = ‘SELECT ‘ + @columnlist + ‘,COUNT(*) -1 FROM ‘ + @tablename + ‘ GROUP BY ‘ + @columnlist + ‘ HAVING Count(*) > 1′

    SET @FINALscript = @ansinulls + @declarestatement
    SET @FINALscript = @FINALscript + ‘ DECLARE dublicate_cursor CURSOR FAST_FORWARD FOR ‘ + @select
    SET @FINALscript = @FINALscript + ‘ OPEN dublicate_cursor ‘ + ‘FETCH NEXT FROM dublicate_cursor INTO ‘ + @variablelist
    SET @FINALscript = @FINALscript + ‘ WHILE @@FETCH_STATUS = 0 BEGIN ‘ + ‘DELETE TOP(@Count) FROM ‘ + @tablename
    SET @FINALscript = @FINALscript + ‘ WHERE ‘ + @wherestatement
    SET @FINALscript = @FINALscript + ‘ FETCH NEXT FROM dublicate_cursor INTO ‘ + @variablelist + ‘ END ‘
    SET @FINALscript = @FINALscript + ‘CLOSE dublicate_cursor DEALLOCATE dublicate_cursor’

    EXEC(@FINALscript)

  161. How to delete the last row from a table in SQL Server 2005 without knowing any column name,u know only table name and it should b in single query.

    i have written this:

    with t as
    (
    select row_number() OVER(ORDER BY (select count(*) from Emp)) as Ranking from Emp
    )

    delete from t where Ranking = (Select max(Ranking) from t)

    this one working fine,but i need it in a single query
    ..
    can any one help me in this.

    Thanks & regards,
    Sumit

      • it is possible i hav already done it but it is not in single query.It is just for my research purpose..

        can u please help me out..

        thanks,
        Sumit

        • What type of research are you doing?
          As you showed in the last example it possible with row_number() only

          Only possible method I can think of is

          delete t from
          (
          select row_number() OVER(ORDER BY (select 0)) as Ranking from emp
          ) as t
          where ranking=1

          • thanks 4 ur reply,but it is deleting the first row not the last row.
            I am trying to take out the most from sql server 2005 which in future i can do some work with that..

            any more suggestion plz..

            tahnks,
            Sumit

  162. Done:
    The final query will be like below:

    delete t from
    (
    select top 1 row_number() OVER(ORDER BY (select 0)) as Ranking from emp
    order by Ranking Desc
    ) as t

  163. when we declare a constraint without giving name to it,then the system automatically n dynamically generates a name.
    Now when we want to drop that constraint we have to get the name from system,is their any way to get that name directly from the system………

    plzzzz help

    thanks,
    Sumit

  164. if there is no key in the table then what ?

    I have on table named [Duplicate] {ID int,FNAME varchar(10),MNAME varchar(10)}

    Here there is no key and here are duplicate rows. so hoca can i delete this duplicate rows.

    Check Data

    ID FNAME LNAME
    1 AAA CCC
    2 BBB DDD
    1 AAA CCC
    2 BBB DDD
    1 AAA CCC
    2 BBB DDD
    3 BCB DGD

    Remove duplicate rows and keep the data in to the table like this using single query.

    ID FNAME LNAME
    1 AAA CCC
    1 AAA CCC
    2 BBB DDD
    2 BBB DDD

    Query :

    with RemoveDuplicate as
    (
    select *,row_number() over(partition by ID,FNAME,LNAME order by ID) as row from DUPLICATE
    )
    delete from RemoveDuplicate where row>1

    • thanks guyz it worked now……

      with RemoveDuplicate as
      (
      select *,row_number() over(partition by num order by num) as row from Duplicates
      )
      delete from RemoveDuplicate where row>1

      this is gr8 ……thanks all

  165. Hi Dave, u r blog is really helpful………….in my interview they asked me this question

    table name is ‘Duplicates’
    and column name is ‘num’

    Select * from Duplicates

    num
    ———–
    1
    1
    1
    1
    2
    2
    2
    2
    2
    3
    3
    3
    3
    3

    (14 row(s) affected)

    Q)how to remove duplicates from the table ….I tried u r given query but didnt work …..can u plz guide me the right procedure, thanks

    DELETE
    FROM Duplicates
    WHERE num NOT IN
    (
    SELECT MAX(num)
    FROM Duplicates
    GROUP BY num)

    when I tried above query its say 0 rows affected

    • thanks guyz it worked now……

      with RemoveDuplicate as
      (
      select *,row_number() over(partition by num order by num) as row from Duplicates
      )
      delete from RemoveDuplicate where row>1

      this is gr8 ……thanks all

  166. Hi guyz i am just wondering is this the right method to try …….based on above example…

    Select * from Duplicates

    select Distinct * into Dup_Rem from Duplicates

    Drop table Duplicates

    if u want to change Dup_Rem to its original name then I did this

    select Distinct * into Duplicates from Dup_Rem

    Select * from Duplicates

    the above few queries worked perfectly and gave me the same output that want it………any suggestions welcome

  167. @Madhivanan

    thanx for the reply bro…..i didnt know abt this function…..probably i’ll do some research…….do you have any tips for me ….coz in couple of days i am attending technical round….thanks once again…..

  168. How to delete duplicate records from the table and there is no
    primary key for the table like..

    id name gender
    1 aaa m
    2 bbb f
    1 aaa m
    3 ccc f
    3 ccc f
    1 aaa m

    I need to delete all the duplicate records such as id 1 is 2 times and id 3 is one time.

    thank you

    • Give only necessary permission to the user
      Another option is to create a view with constant value as one of the columns so that it will not be updated at all

      create view myview
      as
      select columns, 1 as flag from mytable

  169. Hi.
    I’m work c# with sql server 2005, and I have a function Insert in c# but I duplicate the rows in sql.
    how a remove in table sql?????
    I try remove but is don’t remove.
    Help me please

  170. Dear friends

    I have to fetch 10 records from one table to another, my que fetches oly the last record can anyone,correct this pls.

    alter procedure one_temp2
    as
    declare @sno numeric(10),@item varchar(40),@description numeric(20),@so1 numeric(10),@so2 numeric(10)
    set @so2 = 0
    begin
    SELECT @so1= max(sno) FROM one
    while @so1 > @so2
    begin
    print @so1
    select @sno=sno,@item=item,@description=description from one
    insert into two values(@sno,@item,@description)
    set @so2=@so2+1
    while @so1 = @so2
    begin
    break
    end
    end
    end

  171. Shouldn’t the select in the subquery read SELECT MIN(ID)… instead of SELECT MAX(ID)? The MAX(ID) row is the last duplicate entry, correct? To truly remove duplicates, I would think the MIN is what we need. If I have that backwards, let me know.

    Of course, it’s probably a moot point if you’re using an identity ID column. Moot in the sense that the ID column should not have any business purpose.

  172. hi all

    i have four different four table
    e.g. emp_name, emp_add, emp_sal, emp_contact

    i just want the result in a single row
    like

    name —– add —— sal —— contact
    —————————————————
    shiv0——-delhi0—-20000–9876543210
    shiv1——-delhi1—-21000–9876543211
    shiv2——-delhi2—-22000–9876543212
    shiv3——-delhi3—-23000–9876543213
    .
    .
    .
    .
    so on………..

  173. i hope this will help you guys

    /*
    CREATE TABLE [dbo].[userTbl1](
    [UName] [nvarchar](50) NULL,
    [Email] [nvarchar](250) NOT NULL,
    [sex] [nvarchar](20) NULL
    ) ON [PRIMARY]

    GO

    */
    /*
    insert into userTbl1(Uname,Email,sex) values (‘veera’,’veera@cb.in’,’M’)
    insert into userTbl1(Uname,Email,sex) values (‘Bala’,’Bala@cb.in’,’M’)
    insert into userTbl1(Uname,Email,sex) values (‘sakthi’,’sakthi@cb.in’,’M’)
    insert into userTbl1(Uname,Email,sex) values (‘ganesh’,’ganesh@cb.in’,’M’)
    insert into userTbl1(Uname,Email,sex) values (‘ramesh’,’ramesh@cb.in’,’M’)
    insert into userTbl1(Uname,Email,sex) values (‘veera’,’veera@cb.in’,’M’)
    insert into userTbl1(Uname,Email,sex) values (‘Bala’,’Bala@cb.in’,’M’)
    insert into userTbl1(Uname,Email,sex) values (‘sakthi’,’sakthi@cb.in’,’M’)
    insert into userTbl1(Uname,Email,sex) values (‘ganesh’,’ganesh@cb.in’,’M’)
    insert into userTbl1(Uname,Email,sex) values (‘ramesh’,’ramesh@cb.in’,’M’)
    insert into userTbl1(Uname,Email,sex) values (‘veera’,’veera@cb.in’,’M’)
    insert into userTbl1(Uname,Email,sex) values (‘Bala’,’Bala@cb.in’,’M’)
    insert into userTbl1(Uname,Email,sex) values (‘sakthi’,’sakthi@cb.in’,’M’)
    insert into userTbl1(Uname,Email,sex) values (‘ganesh’,’ganesh@cb.in’,’M’)
    insert into userTbl1(Uname,Email,sex) values (‘ramesh’,’ramesh@cb.in’,’M’)
    */
    /*
    select * from userTbl1;
    */
    /*
    set rowcount 1
    delete userTbl1 from userTbl1 a1 where (select count(UName) from userTbl1 a2 where a2.UName =a1.UName)>1
    while @@rowcount > 0
    delete userTbl1 from userTbl1 a1 where (select count(UName) from userTbl1 a2 where a2.UName =a1.UName)>1
    set rowcount 0
    */
    /*
    select * from userTbl1;
    */

  174. i have table. in that table having 100 records but first 10 records no need to display i want display last 90 records tell me frnds
    simple query

  175. Harish you can use Row number command as to select particular record set as mentioned below….

    with CTE_row_values as(
    select field1, field2, row_number() over(order by field1) as row_id from table_name
    )
    select * from CTE_row_values where row_id between 11 and 100

  176. I think to delete duplicate records and leave only single entry,
    set RowCount to 1,then delete query, and finally again set Rowcount=0;by default RowCount=0 to retrieve all Rows
    i.e

    SET ROWCOUNT 1

    delete from Address_Book
    where Address_Book_ID
    in
    (
    select Address_Book_ID from Cust_Det where Cust_Acc_No=’90′
    )

    set RowCount 0

  177. MUCH faster method found:

    I tried the method presented in the article [Method 1], and
    also searched for alternative methods. When I tried one of the alternatives [Method 2], I found it to be much faster. The query cost was less (21% vs. 79% for each in the batch), but the wall clock time showed a much bigger difference (2 seconds vs. 50 seconds).

    They were both run on a subset of real world data with 960,844 rows and 38 duplicates (32 with one dup, 1 with 6 dups). ‘Time’ is a datetime field, while ‘station’ and ‘channel’ are tinyint. The queries were run on a fast dual-processor server.

    I am very concerned with speed since I’ll soon be running this on the full data set which has over 1.5 billion records in it.

    –[Method 1 (Query cost (relative to the batch): 79%]
    –DELETE from #T1
    Select ID from #T1
    WHERE ID NOT IN
    (SELECT MAX(ID) FROM #T1
    GROUP BY time, station, channel)

    –[Method 2 (Query cost (relative to the batch): 21%] gets all dups, leaves original
    –Delete from #T1
    Select ID from #T1
    where ID <
    (Select Max(ID) from #T1 t
    where #T1.time = t.time and
    #T1.station = t.station and
    #T1.channel = t.channel)

  178. Hi all,
    follow this one for deleting duplicate rows in a table…
    I have dept table
    and coumns are deptid,dname

    set rowcount 1
    delete dept from dept a where(select count(*) from dept b where b.deptid=a.deptid and b.dname=a.dname)>1
    while @@rowcount>0
    delete dept from dept a where(select count(*) from dept b where b.deptid=a.deptid and b.dname=a.dname)>1
    set rowcount 0

  179. You could use DENSE_RANK():

    WITH mytableCTE(col1, ranking)

    AS

    (
    SELECT col1, ranking = DENSE_RANK()
    OVER(PARTITION BY col1 ORDER BY NEWID() ASC)
    FROM mytable WITH (NOLOCK)
    )

    DELETE FROM mytableCTE WHERE ranking > 1

  180. DECLARE @T TABLE(ID INT IDENTITY, SName VARCHAR(1000))
    INSERT INTO @T SELECT ‘bhoj’
    INSERT INTO @T SELECT ‘bhoj’
    INSERT INTO @T SELECT ‘raj’
    INSERT INTO @T SELECT ‘gopi’
    INSERT INTO @T SELECT ‘gopi’
    INSERT INTO @T SELECT ‘gopi’
    INSERT INTO @T SELECT ‘ruth’
    INSERT INTO @T SELECT ‘ruth’
    INSERT INTO @T SELECT ‘raj’
    INSERT INTO @T SELECT ‘gopi’

    –SELECT * FROM @T

    DELETE
    FROM @T
    FROM
    @T T
    INNER JOIN
    (
    SELECT ROW_NUMBER() OVER(PARTITION BY Sname ORDER BY SName) DUp,* FROM @t
    ) Temp ON T.ID = Temp.ID WHERE Dup > 1

    SELECT * FROM @T

  181. Hi Guys

    The best method to delete duplicates is CTE’s

    code as follows:-

    select * from my table

    ;With my table2
    ( select row1,
    row_number over() ( partition by row1, order by row1 ) as a

    delete from table2 where a > 1

  182. Assume i have a temp table with some columns (as in the code )and i loaded that temp table with 1 lac record.

    Table’s strucre is like below

    Below table have 1 lac records.

    Now there is one coloumn “PSGRES” also called as Sleep_stage_value, that having value btw 0 to 7 (0,1,2,3,4,5,6,7).

    Now what i want is i want to delete the record that are repeating again.

    Means lets assume First 10 records have “PSGRES” value as 7 and 11th to 14th record having value 1. so i want to delete the record from 2 to 10 as it having the same value 7 and after 12 to 14 as it have same value 2.

    So after deleting those record in temp table , remaing records will be like below.

    Result : (for all 1 lac record )

    1st record with PSGRES value = 7

    11 th record with PSGRES valie = 1

    15 th record with PSGRES value = 2

    and as on till end of the temp table.

    CREATE TABLE #CDISK_MNewFinalAfterForCompare (
    ROWID NUMERIC (8) IDENTITY (1, 1) NOT NULL,
    TRANS_SOURCE_ID VARCHAR(100),
    TRANS_DATE VARCHAR(100),
    STUDYID VARCHAR(50), – Protocol Short Name —
    TRANS_TYPE VARCHAR(50),
    SITE_ID VARCHAR(50), – SITE_NUMBER
    SCREEN_ID VARCHAR(50), – SCREEN_NUMBER
    ALLOCATIONNUMBER VARCHAR(50), – ALLOCATIONNUMBER
    PSG_VISIT_LABLE varchar(50), – VISIT_NUMBER
    VISIT_TYPE VARCHAR(50), – MDB FILE , CUSTOMIZE 4
    ACCESSIONID VARCHAR(100), – MDB FILE , Case_Number
    ACCTUAL_COLL_DATATIME VARCHAR(100),
    PSGTSCD VARCHAR(50), – PSGTSCD
    VEN_LAB_TEST_NAME VARCHAR(50),
    MER_LAB_TEST_ID VARCHAR(50),
    MER_LAB_TEST_NAME VARCHAR(50),
    TEST_STATUS VARCHAR(50),
    COMMENT varchar(255), – COMMENT
    PSGRES VARCHAR(50), – PSGRES
    TEXT_RESULT VARCHAR(50),
    REPORT_UNIT VARCHAR(50),
    RESULT_TYPE VARCHAR(50),
    TRANSFER_TYPE VARCHAR(50),
    DATA_TYPE VARCHAR(50)
    )

    So for that i am using loop and cursor to get this output but it is very slow.

    So can you guys help me out achieve this in query with out using loops and cursor.

    thanks.

  183. Hi Pinal,

    I have a 280 records, it contains repeated 40 records for 7 times. I need to have only one set of 40 records. How can I do this. The total contains nearly 60 Lakhs rows. Please suggest me a good option to do this. Thanks in advance.

    Regards,
    Prabhu

  184. Remove Duplicate Row without PK
    [ Just Copy & Paste ]

    declare @TBL table(EmpID int)

    insert into @TBL(EmpID)
    values(90020),(90020),(90021),(90022),(90023),(90022),(90025),(90022)

    select * from @TBL order by EmpID

    ;with TBLCTE(EmpID,Ranking)
    AS
    (
    select
    EmpID,
    Ranking = DENSE_RANK() over (PARTITION BY EmpID order by newID())
    from @TBL
    )
    delete from TBLCTE where Ranking > 1

    select * from @TBL order by EmpID

  185. Hi guys,

    Having trouble trying to figure out how I can get the ID column of my duplicate records.. ID is unique and required for delete.

    Table is like so:

    ID | Firstname | Surname
    1 Fred Smith
    2 Fred Jones
    3 Alan Thompson
    4 Mary Phillips
    5 Fred Smith
    6 David Jones

    Expected result shows:

    1 Fred Smith
    5 Fred Smith

    Currently I have the code below, works great, but no ID

    SELECT firstname,surname,count(*)
    FROM customer
    group by firstname,surname
    having count(*) > 1

    Is this possible?

    Appreciate any assistance..

    • select t1.* from customer as t1 inner join
      (
      SELECT firstname,surname,count(*)
      FROM customer
      group by firstname,surname
      having count(*) > 1
      ) as t2 on t1.firstname=t2.firstname and t1.surname=t2.surname

  186. Hi,

    Let us assume that we have duplicate records in emp table.

    delete from emp
    where empid not in (select distinct(empid) from emp).

    Think this query works out in deleting the duplicate rows in a table.

  187. DECLARE temp CURSOR FOR SELECT id FROM A GROUP BY A.ID
    DECLARE @id int
    OPEN temp
    FETCH next FROM temp
    INTO @id

    WHILE @@FETCH_STATUS=0
    BEGIN
    IF EXISTS (SELECT id FROM GROUP BY id HAVING count(*)>1 AND id=@id)
    DELETE TOP(SELECT count(*)-1 FROM WHERE id=@id )FROM WHERE id=@id

    FETCH next FROM temp
    INTO @id
    END

    CLOSE temp
    DEALLOCATE temp

  188. Hi,pinal,

    when we handling a large database like 10GB size of records.

    how can i find duplicates and delete that duplicates.

    finding duplicate is not a problem but how to remove those duplicates ?

    Please suggest me.

    Thank u!

  189. Hi,

    Find another method using loop.

    Consider the table #temp as below,
    Id Value
    ——————
    1 100
    2 200
    2 200
    3 300
    3 300
    3 300

    DECLARE @Cnt INT
    SET @Cnt = 1

    WHILE @Cnt IS NOT NULL
    BEGIN
    SELECT TOP 1 @Cnt = COUNT(Id) FROM #Test
    GROUP BY Id,Value
    HAVING COUNT(Id) > 1
    ORDER BY Id,Value

    IF @Cnt IS NULL OR @Cnt = 1 RETURN

    SET @Cnt = @Cnt-1
    SET ROWCOUNT @Cnt

    DELETE t1
    FROM #Test t1
    JOIN
    (SELECT TOP 1 Id,Value,COUNT(Id) Cnt FROM #Test
    GROUP BY Id,Value
    HAVING COUNT(Id) > 1
    ORDER BY Id,Value) t2 ON t2.Id = t1.Id AND t2.Value = t1.Value

    SET ROWCOUNT 0
    END

  190. CREATE TABLE [dbo].[BankMaster](
    [BankID] [int] NOT NULL,
    [Bank Name] [nvarchar](50) NOT NULL,
    [Address] [nvarchar](max) NULL,
    CONSTRAINT [PK_BankMaster] PRIMARY KEY CLUSTERED
    (
    [BankID] ASC
    )
    )

    INSERT INTO BankMaster
    VALUES
    (1,’ING Vysya’,’ING Vysya Eranamkulam’),
    (2,’SBT’,’SBT Eranamkulam’),
    (3,’Federal’,’Federal Bank Eranamkulam’),
    (4,’Canara’,’Canara Eranamkulam’),
    (5,’ING Vysya’,’ING Vysya Eranamkulam’),
    (6,’SIB’,’SIB Eranamkulam’),
    (7,’SBT’,’SBT Eranamkulam’),
    (8,’Canara’,’Canara Eranamkulam’),
    (9,’ING Vysya’,’ING Vysya Eranamkulam’),
    (10,’SBI’,’SBI Eranamkulam’)

    DECLARE @BankName NVARCHAR(50),@Cnt INT

    DECLARE Cursor_Bank CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT BankName,COUNT(BankName) – 1 from BankMaster
    GROUP BY BankName HAVING COUNT(BankName) > 1
    OPEN Cursor_Bank
    FETCH NEXT FROM Cursor_Bank INTO @bankname,@Cnt
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    SET ROWCOUNT @Cnt
    DELETE FROM BankMaster WHERE BankName like @bankname
    SET ROWCOUNT 0
    FETCH NEXT FROM Cursor_Bank INTO @bankname,@Cnt
    END
    CLOSE Cursor_Bank
    DEALLOCATE Cursor_Bank

  191. Dear Pinal,

    Select Distinct Row_Number() Over (Order By CandidateM.CanID Asc)As SlNo, CanCode,CanName from CandidateM Where CandidateM.CanCode like ’09%’
    Using this query how can I take rownumber of a distinct record….?
    regards,

    josekutty varghese

  192. I have a quite different request :

    Below is the table with sample data that I have

    Date Name Product Quantity
    10/11/2011 Aname Abc1 20
    10/10/2011 Aname Abc1 20
    10/9/2011 Aname Abc1 20
    10/8/2011 AName Xyz1 30
    10/7/2011 AName Xyz1 20
    10/6/2011 AName Abc1 20
    10/5/2011 AName Abc1 20
    10/4/2011 BName Xyz1 30
    10/11/2011 Bname Abc2 20
    10/10/2011 Bname Abc2 20
    10/9/2011 Bname Abc2 20
    10/8/2011 BName Xyz2 30
    10/7/2011 BName Xyz2 20
    10/6/2011 BName Abc2 20
    10/5/2011 BName Abc2 20
    10/4/2011 BName Xyz2 30

    Output should be :

    Id Start Date EndDate Name Product Quantity
    1 10/9/2011 10/11/2011 Aname Abc1 20
    2 10/8/2011 10/9/2011 Aname Xyz1 30
    3 10/7/2011 10/8/2011 Aname Xyz1 20
    4 10/5/2011 10/7/2011 Aname Abc1 20
    5 10/4/2011 10/5/2011 Aname Xyz1 30
    6 10/9/2011 10/11/2011 Bname Abc2 20
    7 10/8/2011 10/9/2011 Bname Xyz2 30
    8 10/7/2011 10/8/2011 Bname Xyz2 20
    9 10/5/2011 10/7/2011 Bname Abc2 20
    10 10/4/2011 10/5/2011 Bname Xyz2 30

    I do not have primary key on my Table.

    If you see the number of rows have been reduced.

    I have more than 500 million records and by filtering this I would significantly the number of records in the table.

    Is there any efficient query or sproc to do this, if so could you please help me.

    I have tried using group by , partitions, ranking but nothing worked .

    Thanks & Regards,

    VPandit.

  193. Hi Sir

    I am in a situation where I have the same CustomerID’s but different Customer names. They belong to the same Subsidiary Entity so the phone numbers and address must remain the same..

    Please help

  194. …This is how it looks

    CustomerID Name Address …
    2101 ABC 123 blaa
    2101 WQH 123 blaa

    The Name and Address should remain the same but the CustomerID must be changed

    Thank you

  195. Here is an another query where we could use to delete the duplicate rows…

    delete from table where table.%%physloc%% not in (select MIN(%%physloc%%) from table
    group by duplicate columns)

  196. We can use INTERSECTION on same table to fetch unique records by following query,

    SELECT * into DupRecordsTbl_bkp FROM
    (SELECT * FROM DupRecordsTbl INTERSECT SELECT * FROM DupRecordsTbl)B
    DELETE FROM DupRecordsTbl
    INSERT INTO DupRecordsTbl SELECT * FROM DupRecordsTbl_bkp

    Keeps the unique record into a new table. Deletes the original table records and reinsert the unique records into original table.

    Here we need no info about columns as it compares the whole row.

  197. hoe can improve query performance, hear no need use indexes or vies ect, forexample select * from my table query execution time is 5 min but i want execute it with in a 2min how can it posible, plz give me rply

  198. select distinct * into dbo.table_new from dbo.table_duplicates

    This will copy all distinct rows from one table, create a new table and insert the distinct rows only.

  199. Hi Pinal

    how there can be duplicate rows in a table with identity column.Is this importing same file more than once or some other reason….if it is b’coz of bad database structure….then what it means…plz clearify…

    Thanks
    Divya

  200. Hi all
    I am new in SQL Server and I have a bit different problem. I can’t figure out how to solve. I’ve read some comments but I couldn’t find my problem.

    Suppose that we have table “First_tbl” with column “A_clm” (with 1000 records) and table “Second_tbl” with column “B_clm” (with 200 records).
    I want to delete records which exist in “B_clm” from “A_clm”. I mean finally “A_clm” should have 800 records.
    Can anybody help me about this?

    Thanks

  201. Assuming that a table named “Actual” has redundant data which we wanted to get rid off then

    create a test table

    select * into test from Actual where 1=0

    /* in case if you have an idenity column which is the only thing that differentiates two records then create a table excluding that column. */

    Now populate the distinct records from Actual table into test using

    insert into test
    select distinct * from Actual

  202. Hi,

    I am in urgent need of a solution. My problem is, i need to retrieve the data which has duplicate records and need to have the row number start from 1 to the number of records and start again from 1 for the next start of the duplicate record. Like from the below example, i have the first 2 columns, i need a query to generate the 3rd column

    Ex:

    ProductID Amount RowNumber
    1 200.00 1
    1 330.00 2
    2 400.00 1
    2 600.00 2
    2 350.00 3
    3 200.00 1
    4 330.00 1
    5 200.00 1
    5 330.00 2

    Please help me in solving this.

    Thanks,
    Niranjan

  203. HI Pinal sir,
    Nice,Where i will download these type of SQL queries for interview(Pdf).please let me know the link.

  204. this is used to decrease the quantity from order by expiry date

    “delete from Product where Experddate= (select Experddate from Product where proID=@prID order by Experddate ASC)”

    but it is not working “plz help me” friends

  205. HI
    you can simply use common table expression (CTE).
    in that you need to use Rank funcation

    With t AS
    (
    Select , //in which u want to delete duplicate record.
    RANK(ORDER BY PARTITION BY ) AS ‘Rank’
    from
    )
    DELETE FROM t
    WHERE t.Rank>1

    Thanks

  206. Pingback: SQL SERVER – Select and Delete Duplicate Records – SQL in Sixty Seconds #036 – Video « SQL Server Journey with SQL Authority

  207. different source files has around 5 lakhs- 10 lakhs records with duplicates and final table will be having 5 billion records . How you will remove the duplicate records before inserting it into final table.

  208. Pingback: SQL SERVER – Weekly Series – Memory Lane – #018 | SQL Server Journey with SQL Authority

  209. I believe the best way to delete (multiple) dupes from a table ( that has a primary key) would be as follows:

    Delete from table
    where ID not in
    (Select MIN(id)
    from table
    group by duplicateCol1, duplicateCol2……duplicateColn)

  210. DELETE
    FROM TestTable
    WHERE TestTable.%%physloc%%
    NOT IN (SELECT MIN(b.%%physloc%%)
    FROM TestTable b
    GROUP BY b.column1, b.Column2)

  211. WHILE (SELECT COUNT(*) FROM TBLEMP WHERE EMPNO IN (SELECT empno from tblemp group by empno having count(empno)>1))>1

    DELETE top(1) FROM TBLEMP WHERE EMPNO IN (SELECT empno from tblemp group by empno having count(empno)>1)

    or
    DELETE duplicates FROM
    (SELECT ROW_NUMBER() OVER (PARTITION BY firstname, lastname, EMPNO, salary,dept ORDER BY empno) cnt
    FROM tblEmp) duplicates
    WHERE duplicates.Cnt > 1

  212. Hi,

    I need to remove duplicates from a very big query. I already tried select distinct..but has not worked:

    SELECT distinct Emp.Emp AS ‘Employee ID#’ , Emp.SSNSIN AS ‘Government ID #’, Emp.LastName, Emp.FirstName, Emp.FirstHireDT, Emp.HireDt AS ‘Original Hire Date’, Eml.EmpSts AS ‘Employment Status’, Eml.WrkSts AS ‘Work Status’, Eml.PayGrp AS ‘Payroll Group’, Eml.CstCde AS ‘Cost Code’, Eml.EnrTyp AS ‘Enrollment Type’, Eml.Shf AS ‘Shift’, Eml.Uno AS ‘Union’, Eml.Sup AS ‘ Supervisor #’, Eml.SupName AS ‘Supervisor Name’, Eml.Pos AS ‘Position’, Pos.PosTitle, Eml.SubRegCZ AS ‘Sub Region’, Eml.Ent AS ‘Opco’, Eml.Job AS ‘Job Title’, Eml.Cmp AS ‘Entities’, Eml.Loc AS ‘Location’, Eml.Dpt AS ‘Department’, Eml.Div AS ‘Functional Area’, Eml.Org AS ‘Sub-Functional Area’, Eml.Unt AS ‘Manager’, Eml.SalRat AS ‘Salary Rate’, Eml.PayPer AS ‘Pay Period’, Eml.AnnlSal AS ‘Annual Salary’, PayRng.PayGrd, Eml.ExmptSts, Eml.FTE, (select Max(NextReviewDt) from EmpRvw where EmpRvw.Emp=Emp.Emp) AS ‘Review_Date’, Eml.FileNo, Emp.BirthDt, Emp.HireDt, Emp.TerminationDt, Emp.Gender, Emp.MaritalStatus, (Case When BirthDt<= GetDate() Then FLOOR(datediff(day,BirthDt,getdate())/365.25) Else '0' End) AS Age, Emp.SSNSINCty, Emp.FirstHireDt, Emp.AdjustedHireDt, Eml.EnrTyp, Dpt.DptName, Div.DivName, Org.OrgName, SubRegCZ.SubRegName, Emp.VendorCZ, Emp.CustomerCZ, Emp.Rce, Emp.EMailWork, Emp.CellPhone, Emp.PhoneWork, EmpAdr.Address, EmpAdr.Address2, EmpAdr.Address3, EmpAdr.City, EmpAdr.StateProv, EmpAdr.ZipPostal, EmpAdr.PhoneHome, EmpAdr.EMailHome, (Case When HireDt<= GetDate() Then Round(floor(DateDiff(day,FirstHireDt,GetDate())/365.25)+((floor(DateDiff(month,FirstHireDt,getDate()))-(floor(DateDiff(day,FirstHireDt,GetDate())/365.25)*12))/12),2) Else 0 End) AS Years_of_Service, Eml.TimeSupCZ AS 'Supervisor Name' FROM Emp INNER JOIN Eml ON (Emp.Emp = Eml.Emp AND Eml.EmlEfdDt IS NULL AND Emp.EmpEfdDt IS NULL) LEFT JOIN Job ON (Eml.Job = Job.Job AND Job.JobEfdDt IS NULL) LEFT JOIN PayRng ON (Eml.PayRng = PayRng.PayRng AND PayRng.PayRngEfdDt IS NULL) LEFT JOIN Pos ON (Eml.Pos = Pos.Pos AND Pos.PosEfdDt IS NULL) LEFT JOIN Org ON Org.Org = Eml.Org LEFT JOIN Dpt ON Dpt.Dpt = Eml.Dpt LEFT JOIN Div ON Div.Div = Eml.Div LEFT JOIN EmpAdr ON EmpAdr.Emp = Emp.Emp LEFT JOIN SubRegCZ ON SubRegCZ.SubReg = Eml.SubRegCZ WHERE (Eml.EmlEfdDt IS NULL AND Emp.EmpEfdDt IS NULL AND Eml.EmlEfdDt IS NULL AND Job.JobEfdDt IS NULL AND PayRng.PayRngEfdDt IS NULL AND Pos.PosEfdDt IS NULL)

    can someone please help me~! It is very urgent …

  213. CREATE TABLE TABLE1 (ID INT,Name char(10));

    Insert Into Table1 (ID,Name) Values(1,’Dipak’)
    Insert Into Table1 (ID,Name) Values(2,’Jyoti’)
    Insert Into Table1 (ID,Name) Values(1,’Dipak’)
    Insert Into Table1 (ID,Name) Values(3,’Veera’)
    Insert Into Table1 (ID,Name) Values(2,’Jyoti’)
    GO

    DELETE DrowNo FROM
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY Id, name ORDER BY Id) cnt
    FROM Table1
    ) DrowNo
    WHERE DrowNo.Cnt > 1
    Go

    Select * From Table1
    Go
    DROP TABLE Table1
    GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s