The corelated query in TOP clause is not working .. Basharat Bhat has given only for single dup entry

Bals,

Can you please post the code you are having an issue with?

Thank you,
The MSSQLTips.com Team

There is another very good way to do this, in SQL Server 2005, if you do not have the key set up, but want to effectively use a column or more as your primary key.

 ;with DelDup as (select row_number() over (partition by
FirstName, LastName order by FirstName, LastName) as RowNofrom duplicateTest)
Delete from DelDup where RowNo> 1

--alternate--

You can easily adjust this to use one column or as many as you wish, as long as you update the column(s) after the partition by and after the order by.

Thank you both for the feedback.

Thank you,
The MSSQLTips.com Team

Hi Friend Pls test the following one, dis s a very simple method

Delete Top (Select Count(*)-1 From EmpTest Where EmpId=A.EmpId)
From EmpTest As A

It didnt work Prakash,

Hai,  can u try this code?

DELETE FROM TestTable WHERE
EXISTS
(SELECT Sid FROM TestTable T WHERE T.Tname = TestTable.Tname AND I.SId < TestTable.SId)

hi,

im facing one problem in ur example you knows the duplicate data's key value so you can delete easily.

What my question in there is 1000's records with few duplicate (in composit key one is different) then how to extract the that particular duplicate  records and delete it

This post have been posted for more than two year but it helped me alot. Thanks.

There are duplicate records in Fact and Facterror table(both table have same records in them some 100 records).i need to delete the records from facterror table and insert it into Fact table.

Gopi, not sure what you are asking.

If the records already exist in both the FACT table and FACTERROR then can you just delete the records from the FACTERROR table?

how to delete duplicate using phpmyadmin?is working when insert data,data can not read..that he publish 1234567 are duplicate primary key and when insert 3456789,he also publish 1234567?

how i can i do?plz

Hi Erni, not exactly sure what you need to do.

If you are trying to eliminate duplicate primary key values in a table, make sure the table has a PRIMARY KEY setup so there is not a chance to get duplicate records.  The front end application would need to handle the error message from SQL Server, but this would ensure you don't have duplicates.

I want To delete Secound Record  how i can delete:

Hi Kisan,

if the records are identical it doesn't really matter which record you are deleting also you have no control over what record SQL Server will present to you.

If there are other columns in the table that have different values then you can add that to the where clause if not you won't have any real control.

You can also look the post above from Gadi35.

I got your answer 

 if i  delete record it will delete both  beczz their is no. primary key use it...

if i m taking any column so i cant delete but thier is redudency  it will delete both record......

so telll me sir what synatx should i use in delete query to deleted secound record..

Kisan - if you run this query this will delete only one of these records. Since they are identical it doesn't matter which one you delete.

DELETE TOP(1) FROM dbo.duplicateTest WHERE ID = 1

Greg, that last code will cancel anything that exists and only leave the duplicates.

Gadi35 - yes you are correct that it will only delete one record.  I was referring to the example that "kisan" listed that just had two records, so this would delete one of those two records, but I agree if there were more than two duplicates only one record would be deleted.

thanku...  sir.......

this  i try this query it will work fine....... thanku muchhhhhh..

sir I have One More Question

Suppose i create auto id like

create table demo

(

 id int identity(1,1) primary key

)

if i inserted  5 records and i delte  record Number 5

so agin if i inserted  record so it will give id no:6

but i want  id 5

so how will be do sir....

Kisan - if you delete records from a table that uses an identity column you are going to have gaps in the numbering.

If there were only 5 records in the table 1,2,3,4,5 and you delete record number 5 the next identity value will still be 6.  You could use the RESEED option to fix this and make the next number 5 again.  Take a look at this tip: http://www.mssqltips.com/sqlservertip/1123/managing-and-maintaining-sql-server-identity-values/

But if you have records with values 1,2,3,4,5 and you delete record number 3 you will still have a row without a number 3 value.  To get around this you could use SET IDENTITY INSERT.  Take a look at this tip: http://www.mssqltips.com/sqlservertip/1061/using-identity-insert-to-keep-sql-server-table-keys-in-sync/

Seems like you could also use select top 1 

Nice Job