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)

How do you implement session management in SQL Server mode?

0 Answers   Cap Gemini,

Hi Friends, I have a table in which there are thousands of records and in city field there is NULL value for all records now i want to change that null value with distinct values in each record say delhi, bihar, agra, jaipur etc, what will be the query for that????? its not possible to update thousands of records one by one. is there any alternative ...? Plz help ... its urgent Thanx in advance

1 Answers  

What is built-in function?

0 Answers  

What are all new concepts in SQL Server 2008? That is Exactly Difference between 2005 to 2008

2 Answers   IBM,

when would you go for denormalization? : Sql server database administration

0 Answers  

explain the storage models of olap? : Sql server database administration

0 Answers  

Explain indexing and what are the advantages of it?

0 Answers  

What are the different types of Indexes available in SQL Server?

0 Answers  

Can you explain what are various ways to enhance the ssrs report?

0 Answers  

How to delete existing rows in a table?

0 Answers  

How many types of stored procedures are there in sql server?

0 Answers  

What structure can you implement for the database to speed up table reads?

0 Answers