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

i want to create procedure for create table in sql server 2005 for example create procedure create_table @table varchar(20) as create @table( id int, name char(20) ) but it will get error what is solution?

5 Answers   Aptech,

What different steps will a sql server developer take to secure sql server?

0 Answers  

What stored by the master?

0 Answers  

WHAT OPERATOR PERFORMS PATTERN MATCHING?

2 Answers   CarrizalSoft Technologies, CTS,

Is it safe to delete log files?

0 Answers  

1.what is the purpose of using alias name in sql server? with certain example? 2.what is cursor?will u plz explain me in detail with certain example?

3 Answers  

explain databases and sql server databases architecture? : Sql server database administration

0 Answers  

i have 4 tables.. T1, T2, T3, T4.. these tables have the same structure and they store the information entered in different years.. T1 stored 2002, T2 stored 2003, T3 stored 2004 and T4 stored 2005.. i want to copy contents in T1 to T2, T2 to T3, T3 to T4 and T4 to T1.. how do i do that? Temp tables cannot be used..

4 Answers  

How can we Use Linked Server? Uses of Linked server

1 Answers   IBM,

What happens if null values are involved in bitwise operations?

0 Answers  

difference between Clustered index and non clustered index ?

0 Answers   Infosys,

How can you tell if a database object is invalid?

0 Answers   MCN Solutions,