Answer / kala suresh

--BY USING A TEMP TABLE TO DELETE DUPLICATE ROWS OF A TABLE
WITH OUT A PRIMARY KEY COLUMN OR AN IDENTITY COLUMN

SELECT Name, age, ROW_NUMBER() OVER(ORDER BY age) AS
'RowNumber' into #temp1 FROM RowNumber

delete from #temp1 where rownumber not in(select
max(rownumber) from #temp1 group by name,age)

delete from rownumber

select * from #temp1 into rownumber

drop table #temp1


--BY USING AN IDENTITY COLUMN TO DELETE DUPLICATE ROWS OF A
--TABLE WITH OUT A PRIMARY KEY COLUMN

delete from rownumber where id not in(select min(id) from
rownumber group by name,age)
select * from rownumber

Answer / sybaseexperties

Mr. Parashu, Ur query will delete all dup rec at all. the
solution should be for del dups but keepning single record.
understand?? :)

Answer / srinivasan

WITH CTE AS
(
SELECT * ,ROW_NUMBER() OVER (PARTITION BY ID,NAME
ORDER BY ID DESC) AS RNUM FROM TABLE A
)

DELETE CTE WHERE RNUM >1

IF U HAVE ANY MORE DOUBTS
MAIL ME MCAVASAN@GMAIL.COM

Answer / manoj

DELETE FROM table_name a
WHERE ROWID >(SELECT min(ROWID)
FROM table_name b
WHERE a.col_1=b.col_1 )

Answer / pavan kumar

Mr. Manoj, the system throws error message as "Incrrect
sysntax near 'a'"

Answer / g2

create table Table1(trowid int not null, tname varchar(100))

insert into table1(trowid, tname) values(1, 'G2')
go 100

declare @row int;
set @row= (select count(*) from table1)
set @row=@row-1
set rowcount @row
delete from table1 -- You can put here conditions with all
the columns also
set rowcount 0
go

Answer / arif jameel

ADD a new identity_column (1,1) on <table>
by

alter table <table_name>
add <Identity_column> int identity(1,1)

delete from <table> where <identity_Column> in
(select max(<identity_Column>) from <table>
group by any <table_column_name>)

drop <identity_column>

Answer / jagan mohan varma

employee
----------------------------
eid ename sal
----- --------- ------
1 jagan 2000
2 mohan 3000
3 varma 4000
********************************
attendence
-------------------------------
id eid date
----------- ----------- -----------------------
10 1 2010-06-10 00:00:00.000
11 1 2010-06-10 00:00:00.000
12 2 2010-06-10 00:00:00.000
13 2 2010-06-10 00:00:00.000
14 3 2010-06-10 00:00:00.000
15 3 2010-06-10 00:00:00.000
16 3 2010-06-10 00:00:00.000
*************************************************

Deleting duplicate records from attendence table could be:
**********************************************************

delete from attandence
where id not in (
select max(atd.id)
from employee emp
inner join attandence atd
on atd.eid = emp.eid group by atd.eid)

Answer / sudhagar

Delete from (select * from <TABLE_NAME> where rowid not in
(select min(rowid) from <TABLE_NAME> group by c1,c2...))

Answer / anil sharma

In sqlserver 2000 we must use a unique id per row then we
can delete duplicate rows.

delete from <tablename> where <rowid> not in (select min
(<rowid> from <tablename> group by co1,col2.)


But in sqlserver 2005 there a function RowId.Which is same
as above concept.It return unique id per row.

wat wil hapn if we give the both read and deny read permission to user?

0 Answers   IBM,

i have a table #temp1(id, Name groupname ) and record like this 1 R1 S 2 R3 S 3 R2 S 4 R4 D 5 R5 D 6 R6 K 7 R7 K 8 R8 L 9 R9 L 10 R10 L 11 R11 K and i want to display record based on user defind sorting order e.g. 1 R4 D 2 R5 D 3 R6 K 4 R7 K 5 R11 K 6 R1 S 7 R3 S 8 R2 S 9 R8 L 10 R9 L 11 R10 L

8 Answers  

Do you know what is replace and stuff function in sql server?

0 Answers  

What are the functions in sql server?

0 Answers  

What are filegroups in sql server?

0 Answers  

Where can you add custom error messages to sql server?

0 Answers  

What is the difference between UNION and UNIONALL?

5 Answers   CarrizalSoft Technologies, CTS,

How to add an address record into adventureworkslt?

0 Answers  

What will happen if a column containing char type data is changed to the nchar data type?

0 Answers  

what are default? Is there a column to which a default cant be bound?

1 Answers  

What are the High-Availability solutions in SQL Server and differentiate them briefly?

0 Answers  

how to connect sybase to sql server 2005?.

1 Answers   ABC,