Answer / dilip

Ans for 1st Query
How to retrieve duplicate rows in a table?

SELECT * FROM EMP1 WHERE (EMP_ID IN (SELECT emp_id FROM
emp1 GROUP BY emp_id HAVING COUNT(emp_id) > 1))

Answer / skybeaver

/* selecting duplicate rows in a table */
select col1, col2, ..., colN, count(*)
from TableName
group by col1, col2, ..., colN
having count(*) > 1

/* deleting duplicate rows from a table */
select col1, col2, ..., colN, count(*) as "Duplicates"
into #duplicates
from TableName
group by col1, col2, ..., colN
having count(*) > 1

delete TableName
from TableName t, #duplicates d
where t.col1 = d.col1 and
....
t.colN = d.colN

/* damn I'm good! */

Answer / anil kumar karasi

1.
Select from <table name A>
where rowid > (select min(rowid) from <table name B>
where A.keyvalues=B.keyvalues);


2.
Delete from <table name A>
where rowid > (select min(rowid) from <table name B>
where A.keyvalues=B.keyvalues);

Answer / mohit d jethva

///////////Cursor for delete duplicate record in
table//////////

DECLARE @ID int
DECLARE @NAME NVARCHAR(50)
DECLARE @COUNT int

DECLARE CUR_DELETE CURSOR FOR
SELECT [ID],[NAME],COUNT([ID]) FROM [Example] GROUP BY [ID],
[NAME] HAVING COUNT([ID]) > 1

OPEN CUR_DELETE

FETCH NEXT FROM CUR_DELETE INTO @ID,@NAME,@COUNT
/* Loop through cursor for remaining ID */
WHILE @@FETCH_STATUS = 0
BEGIN

DELETE TOP(@COUNT -1) FROM [Example] WHERE ID = @ID

FETCH NEXT FROM CUR_DELETE INTO @ID,@NAME,@COUNT
END

CLOSE CUR_DELETE
DEALLOCATE CUR_DELETE

Mohit D Jethva

Answer / madhur/amrutha

select ROW_NUMBER() OVER (ORDER BY names ASC) AS ROWID, *
into #temp from emp
select * from #temp where ROWID not in(
select b.ROWID from
(
select ROW_NUMBER() OVER (ORDER BY names ASC) AS ROWID, *
from emp
except
SELECT ROW_NUMBER() OVER (ORDER BY names ASC) AS ROWID, *
FROM
(
select names , sal from emp
union
select distinct names,sal from emp) as a ) as b)

drop table #temp

Answer / nagabhushan adhikari

These are duplicates....

select distinct(col1),col2,col3.... into #temp from table
group by col1 having count(1) > 1

by the below delete duplicates
delete table from table A, #temp B where A.col1= B.col1

by this insert only a single record
insert into table select col1, col2,col3... from #temp

Answer / sameer

--select * from #TempR
select * into #temp2 from #TempR

-- select * from #temp2

alter table #temp2 add record_id numeric(5,0) identity not
null

/* select those row which are repeated */

select * into #qwe
from #temp2
where exists(

select null from #temp2 b
where b.ID = #temp2.ID
and b.TYPE = #temp2.TYPE
group by b.ID, b.TYPE
having
count (*) >=2
)

--select * from #qwe




/* delete those row which are repeted */

delete from #TempR where ID in ( select ID from #qwe)

/* insert those row which are deleted */

delete from #qwe where record_id not in (

select record_id
from #qwe
group by ID, TYPE
having record_id = max (record_id)
)

-- select * from #qwe
alter table #qwe drop record_id

insert into #TempR
select * from #qwe

/* see output */


select * from #TempR

/* check for row getting repeted */


select *
from #TempR
where exists(

select null from #TempR b
where b.ID = #TempR.ID
and b.TYPE = #TempR.TYPE
group by b.TT_ID, b.EQP_TYPE
having
count (*) >=2
)

Answer / madhur/amrutha

Consider a table emp with employee details. The Correct
code to retrieve duplicate rows :


select distinct * from emp where names in
(select names from emp group by names having count(sal)>1)

Answer / samba shiva reddy . m

How to retrieve the duplicate rows in table :

select * from emp group by empid,empname having count(empname)>1

How to delete the duplicate rows in table :
1.inserting distinct rows in temp table variable
2. deleting emp table
3.Again inserting into emp table from temp table variable


declare @temp table
(empid int,empname varchar(50))
insert into @temp
select * from emp group by empid,empname having count(empname)>0
delete from emp
insert into emp
select * from @temp
select * from emp

Answer / lince thomas

it is very simple,,below
------------------------
select * from(
select row_number() over(partition by empname order by
empname)as rno,empname,salary from Emp1Pay)T
where T.rno>1


delete from Emp1Pay where empid=
(select empid from(select row_number() over(partition by
empname order by empname)as rno,empid
from Emp1Pay )T where T.rno>1)

What will be query used to get the list of triggers in a database?

0 Answers  

How to Create Login and User through query in Sql server 2005.and also how to delete Login and User through query?

2 Answers  

Do you know what is difference between index seek vs. Index scan?

0 Answers  

What is difference between foreign key and unique key?

0 Answers  

What is data integrity? Explain constraints?

1 Answers   Thinksoft,

What is the difference between dataadapter and datareader?

0 Answers  

What is the beast way to write CTE in SQL Server ?

0 Answers   HCL,

What are the two authentication modes in sql server?

0 Answers  

Explain triggers?

0 Answers  

What is the function of inner join?

0 Answers  

What is use of except clause?

0 Answers  

Which tcl commands are available on the sql server?

0 Answers