Answer / deepak raheja

DELETE FROM A
WHERE (id NOT IN
(SELECT MAX(ID)
FROM A
GROUP BY name))

suppose i have table called A. A has two coloumn id
(identity) as int and Name as nvarchar. I have data like
this.

id Name
1 C
2 D
3 C
4 E
5 D
6 D

Answer / sagun sawant

Name Age
chandran 23
ranjith 24
chandran 23

To delete one of the duplicate records use following query

(For sql server 2000)

Set rowcount 1
delete from [tableName] order by name
set rowcount 0
--Write a cursor to delete multiple duplicate records
Or (In sql server 2005)

;with DelDup as (select row_number() over (partition by
sname order by sname) as RONO ,sname from [TableName])
Delete from DelDup where RONO > 1

Answer / vijay

WITH INTER_TABLE(ROW, COL1) AS(
select row_number() over (partition by
COL1 order by COL1) as ROW ,COL1 from [TABLE_NAME]
)
Delete from INTER_TABLE where ROW > 1

Answer / pavan kumar

Mr. Chandran, I tried your query but the system is throwing
error message as 'Incorrect syntax near the
keyword 'group''.

Answer / rama krishna

This will help u a lot bcz dynamically it will delete
duplicates



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)


select * from tb1


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

Answer / bala_it243

using the following query u can delete the duplicate records.
but it is available only on SQL Server 2005.

with mytable as
(
select *, ROW_NUMBER() OVER(partition by id,myname order by
id desc) as RowNumber from test
)
delete from mytable where rownumber>1


"Have a Great Day"

Bala

Answer / ben mccameron

I have a query that I use with success I have a table with
telephone numbers in it and sometimes I have duplicate
phone numbers in the table... here is how I see what they
are and then remove them.

===========================================

select telephone
from Table_A
group by telephone having count(*) > 1

SET ROWCOUNT 1
SELECT NULL
WHILE @@rowcount > 0
DELETE step
FROM Table_A as step
INNER JOIN
(SELECT telephone
FROM Table_A
GROUP BY telephone HAVING count(*) > 1)
AS t ON t.telephone = step.telephone
SET ROWCOUNT 0

===========================================

This query will actually find and remove the duplicates
from Table_A but will not remove both instances it will
only remove one... leaving you with one good record... hope
this helps someone. : )

Answer / abc

SELECT distinct column_names INTO temp_table FROM main_table
drop table main_table
sp_rename temp_table,main_table

Answer / sumit

I have The Same Problem And I Have Done Woth This


DECLARE @empid int, @empname varchar(50),@Cnt int

DECLARE duplicate_cursor CURSOR FOR

-- select all columns in table bit you must have an count column
select empid,empname, count(*) Cnt
from tbl_Temp
group by empid, empname
Having count(*) > 1

OPEN duplicate_cursor

FETCH NEXT FROM duplicate_cursor
INTO @empid, @empname,@Cnt

WHILE @@FETCH_STATUS = 0
BEGIN

SET @Cnt = @Cnt - 1

SET ROWCOUNT @Cnt

DELETE tbl_Temp
WHERE @empid = empid AND @empname = empname


FETCH NEXT FROM duplicate_cursor
INTO @empid, @empname
END

CLOSE duplicate_cursor
DEALLOCATE duplicate_cursor

-- dont forget to set rowcount to 0
SET ROWCOUNT 0

Answer / anuj dhingra

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

What is @@rowcount in sql?

0 Answers  

What is normalization in Database ?

4 Answers  

What is bcnf normalization form?

0 Answers  

Different types of keys in SQL?

0 Answers   Infosys,

Explain the purpose of indexes?

0 Answers  

What is difference between clustered and non clustered index?

0 Answers  

Explain what is the main purpose of having conversation group?

0 Answers  

How much space does sql server 2016 take?

0 Answers  

How to create view in stored procedure sql server?

0 Answers  

While using a cursor, how can you differentiate between a deleted row and a row that has been inserted with null data values?

0 Answers  

How to add more data to the testing table in ms sql server?

0 Answers  

What is mean by dml?

0 Answers