Answer / tallapaneni

create table emp(eid int, ename varchar(50),age int)

insert into emp values( 1,'ramu1,21)
insert into emp values( 1,'ramu2,22)
insert into emp values( 2,'ramu3,22)
insert into emp values( 3,'ramu4,24)
insert into emp values( 4,'ramu5,25)
insert into emp values( 5,'ramu6,26)
insert into emp values( 2,'ramu7,27)
insert into emp values( 6,'ramu8,28)
insert into emp values( 6,'ramu9,29)

SELECT * FROM emp

WITH aaa AS (SELECT (ROW_NUMBER() OVER (PARTITION BY eid
ORDER BY eid)) AS RNum FROM emp)
DELETE FROM aaa WHERE RNum IN (SELECT a.RNum FROM aaa AS a,
aaa AS b WHERE a.RNum > b.RNum GROUP BY a.RNum)

SELECT * FROM emp order by eid



Regards,
Raaam

Answer / sujit kumar suman,new delhi

DELETE DUPLICATE RECORDS FROM TABLE IN SQL SERVER:

DELETE FROM TABLE1
WHERE
ID NOT IN
(SELECT MAX(ID) FROM TABLE1
GROUP BY NAME)

WHERE ID IS IDENTITY FIELD AND NAME IS DUPLICATE FIELD

TRY THIS QUERRY AND ENJOY.......

Answer / inder kumar singh

1) If all coumns are duplicate
create tablet tbl_deleteDuplicate
(
emp_id numeric,emp_name varchar(100)
)

insert into tbl_deleteDuplicate values(1,'a')
insert into tbl_deleteDuplicate values(1,'a')
insert into tbl_deleteDuplicate values(2,'b')
insert into tbl_deleteDuplicate values(3,'c')
insert into tbl_deleteDuplicate values(4,'d')
insert into tbl_deleteDuplicate values(4,'d')
insert into tbl_deleteDuplicate values(5,'e')
insert into tbl_deleteDuplicate values(5,'e')
insert into tbl_deleteDuplicate values(6,'f')

set rowcount 1
while @@rowcount > 0
delete a from tbl_deleteDuplicate a where
(select count(*) from tbl_deleteDuplicate b
where a.emp_id = b.emp_id)>1
set rowcount 0
select * from tbl_deleteDuplicate
set nocount off

2) if value column is duplicate

truncate table tbl_deleteDuplicate
insert into tbl_deleteDuplicate values(1,'a')
insert into tbl_deleteDuplicate values(2,'a')
insert into tbl_deleteDuplicate values(3,'b')
insert into tbl_deleteDuplicate values(4,'c')
insert into tbl_deleteDuplicate values(5,'d')
insert into tbl_deleteDuplicate values(6,'d')
insert into tbl_deleteDuplicate values(7,'e')
insert into tbl_deleteDuplicate values(8,'e')
insert into tbl_deleteDuplicate values(9,'f')
insert into tbl_deleteDuplicate values(10,'f')
insert into tbl_deleteDuplicate values(11,'f')

delete tbl_deleteDuplicate where emp_id not in
(
select max(emp_id) from tbl_deleteDuplicate group by
emp_name having count(emp_name)>1
)
and emp_id not in
(
select max(emp_id) from tbl_deleteDuplicate group by
emp_name having count(emp_name)=1
)

select * from tbl_deleteDuplicate

Answer / rajnish kumar pandey

WITH TempTable(Col1, Col2, DuplicateCount)
AS
(
SELECT Col1, Col2,
ROW_NUMBER() OVER(PARTITION BY id, roles ORDER BY id) AS
DuplicateCount
FROM MainTable
)
DELETE
FROM TempTable
WHERE DuplicateCount > 1
GO

Answer / sunil

CREATE TABLE [dbo].[DupTable](
[Name] [nchar](10) NOT NULL,
[Age] [int] NOT NULL
) ON [PRIMARY]

-- Insert the duplicate data into the table...



Select * from DupTable
go

with Emp AS
(
Select Name,Age, ROW_NUMBER() over (order by Name) as
RowNumber FROM DupTable
)
Delete t1 From Emp as T1, Emp as T2
where T1.Name = T2.Name AND T1.Age= T2.Age and T1.RowNumber
> T2.RowNumber
go

Select * from DupTable
go

Answer / prem

Hi All,

I want to display all duplicate records in the table. My
query has to fetch all the records which are duplicate(First
Name or Last Name). Also I want the ability to also pull
names where there might be a middle initial placed in the
end of the first name field, (i.e., "Maria Z. " vs. "Maria")
as well.

Please guide me to find this.



Table:

ID FirstName LastName

1 Zach H Hoffman

2 Zach Hoffman

3 Troy Hoffman

4 Shawn Livermore

5 Prem S

6 Jony Hoffman H

7 Zach Modan



I need the query to filter.........

ID FirstName LastName

1 Zach H Hoffman

2 Zach Hoffman

3 Troy Hoffman

6 Jony Hoffman H

7 Zach Modan



I hope this example will give you clear idea.....




Thanks in Advance

Prem

Answer / mahesh babu ummaneni

inthis we have some steps
-->first rimove duplicate values from table
select distinct * from ta1
-->copy the data into anothetable
select distnice * into tb2 from tb1
-->remove the first table
drop table tb1
-->copy the tb2 data into tb1
select distinct * into tb2 from tb1

Answer / prasant ojha

create a table tbl_info
(
name varchar(250),
age int
)
Enter values

'RAM',25
'SHYAM',34
'RAM',25
'GHANSHYAM',25
'RAM',25

//SQL QUERY TO REMOVE DUPLICATE RECORDS WITHOUR ANY KEY
with info_tbl as(

SELECT *,row_number() over(partition by name order by name)
as record_no from tbl_info
)
delete from info_tbl where record_no>1

Answer / kalyan

with [TN ordered by CN] as
(
select row_number() over(partition by CN order by CN) as
rowid,* from TN
)
delete from [TN ordered by CN] where rowid > 1

Answer / nag

create table MMM(Name varchar(20),age int)
select * from MMM
insert into MMM values('MIKE',20)

insert into MMM values('Shuck',30)

insert into MMM values('Shuck',30)

insert into MMM values('Sh',90)
insert into MMM values('Shuc',60)
insert into MMM values('Sonu',70)
insert into MMM values('Siddu',80)
insert into MMM values('nagu',50)

SELECT name,age
FROM mmm
GROUP BY name,age
HAVING count(*) <=1

Try this query friends it will work out

How to loop through returning rows?

0 Answers  

Explain transaction server implicit?

0 Answers  

When would you use it?

0 Answers  

can we call functions from stored procedure in SQL Server 2005 ? How?

3 Answers  

What are the methods used to protect against sql injection attack?

0 Answers  

what is a join? : Sql server database administration

0 Answers  

Once setting replication, can you have distributor on sql server 2005, publisher of sql server 2008?

0 Answers  

What are the steps to take to improve performance of a poor performing query? : sql server database administration

0 Answers  

Which command is used for user defined error messages?

0 Answers  

Please get the 4 th maximum salary from a table without using any sql keyword (TOP,MAX are mot allowed)

8 Answers  

How many databases instances are there in sql server 2000?

0 Answers  

What are the types of indexes available with SQL Server?

2 Answers   Standard Bank,