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 Improve the performencs of SQL Server 2005 exclude stored Procedure and Indexes?

0 Answers  

Can binary strings be used in arithmetical operations?

0 Answers  

How to insert stored procedure result into temporary table?

0 Answers  

What is normalization according to you and explain its different levels?

0 Answers  

Explain the working of sql privileges?

0 Answers  

Do you know how to store and query spatial data?

0 Answers  

Where is trigger in sql server?

0 Answers  

can anyone explain me the concept of Serialization in Detail and Clear? plz its urgent i have interview on friday (15th feb)

1 Answers  

Hi, I Created 3 Tables Person(PersID[prkey],Name,Email,Password), Project(ProjName,ProjID[prkey],ProjLeader,ProjManager) & ProjectInvolvement(EntryDate,ProjID[frkey],PersID[frkey],ProjDuration). For this how can i INSERT,UPDATE & DELETE Through PROCEDURE? Please Post the Answer for me. Desai.

0 Answers  

What is the difference RDBMS and Graph Database?

0 Answers   EXL,

What happens if null values are involved in arithmetic operations?

0 Answers  

What is the difference between ddl and dml?

0 Answers