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);

///////////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

delete from employee 
where Emp_id not in (select min(Emp_id) from employee 
group by Emp_Name)

/* 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! */

drop table #TEMP select distinct * into #TEMP from 
TABLE_NAME delete from TABLE_NAME insert into TABLE_NAME 
select * from #TEMP

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))

--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
  )

begin 
select distinct * into #one  from four  where id in (select 
id from four group by id 
having count(*)>1)
delete from four where id in (select id from four group by 
id   having count(*)>1)
insert into four select * from #one
end

Tbale emp had some duplicate entries and i wanted to retain 
the first of all duplicates, the others could be deleted as 
follows . Please give your comments if this is the most 
optimum way :

delete from emp where rowid in 
(select rowid from emp o where rowid != 
 (select min(rowid) from emp i where i.empno=o.empno));

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

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)

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