how delete duplicate rows from table without primary key

Hi,

I have doubt. I am creating table and insert two rows.

This is my table .

BookName Price Author

C++ 150 kanithkar
C++ 150 kanithkar

In the table no primary key. I need to delete duplicate rows without primary key and my output would be like

BookName Price Author

C++ 150 kanithkar

Give a hintto me.Hope your's reply.

Thanks

Hello Ashok,
Kindly look at the below links.
http://www.mssqltips.com/tip.asp?tip=1103
http://www.sql-server-performance.com/articles/dba/delete_duplicates_p1.aspx
http://sql-server-performance.com/Community/search/SearchResults.aspx?q=Delete+duplicate&a=0

create table #temp1 (BookName varchar(20) null, Price int null, Author varchar(20) null)

insert into #temp1 (BookName,Price,Author)
values ('C++', 150, 'kanithkar')
insert into #temp1 (BookName,Price,Author)
values ('C++', 150, 'kanithkar')
insert into #temp1 (BookName,Price,Author)
values ('C+', 150, 'kanithkar')
insert into #temp1 (BookName,Price,Author)
values ('C+', 150, 'kanithkar')
insert into #temp1 (BookName,Price,Author)
values ('C', 150, 'kanithkara')
insert into #temp1 (BookName,Price,Author)
values ('C', 150, 'kanithkara')

select * from #temp1
BookName Price Author
-------------------- ----------- --------------------
C++ 150 kanithkar
C++ 150 kanithkar
C+ 150 kanithkar
C+ 150 kanithkar
C 150 kanithkara
C 150 kanithkara
(6 row(s) affected)
SET NOCOUNT ON
declare @BookName varchar(20)
Declare KURSOR cursor for select distinct BookName from #temp1
OPEN KURSOR
FETCH NEXT FROM KURSOR INTO @BookName
WHILE @@FETCH_STATUS = 0
BEGIN
Print @BookName
DELETE TOP (SELECT COUNT(*) -1 FROM #temp1 WHERE BookName = @BookName)
FROM #temp1
WHERE BookName = @BookName
FETCH NEXT FROM KURSOR INTO @BookName
END
CLOSE KURSOR
DEALLOCATE KURSOR
SET NOCOUNT OFF
select * from #temp1
BookName Price Author
-------------------- ----------- --------------------
C++ 150 kanithkar
C+ 150 kanithkar
C 150 kanithkara
(3 row(s) affected)

Oops! forgot to drop #Temp1 []
select * from #temp1
drop table #temp1

Thanks dude. Great its working []

Madhi - not sure the row_number() solution will "always" be quicker. I have my head still stuck firmly in SQL 2000, and am still thinking of everything except row_number() and CTEs.
Apart from that, the proper answer should include the comment that all tables need to have a unique key, and if there is no apparent candidate key that you can add an identity field as the row identifier.

Ya! will keep that in mind... []

Try this...
Alter table mytable add temp_id int identity (1, 1) not null
delete from mytable where temp_id not in(
Select max(temp_id) from mytable
group by c1, c2, ..., cn) --(Include all the columns which you want to filter for duplicates )
Alter table mytable drop column temp_id
Just cross check it for any syntax errors.....

Create Table #Tbl(BookName VarChar(50), Price Int, Author VarChar(50));
Go
Insert Into #Tbl Values ('C++', 150, 'kanithkar'), ('C++', 150, 'kanithkar');
Go
Select * From #Tbl;
Go
With T As (Select ROW_NUMBER() Over (Partition By BookName,Price,Author Order By BookName) Num From #Tbl)
Delete From T Where Num>1;
Go
Select * From #Tbl; Go

.

Hi,
we can do it in many ways,
Steps to remove duplicate tables:
Suppose your table name is tblMain.
-----------------------------------------------------------------------
select distinct * into #tempTable from tblMain --Selecting distinct rows and store into Temp table
Truncate table tblMain --Truncating MainTable
insert into tblMain Select * from #tempTable --Inserting data into your maintable by selecting from Temp Table
--------------------------------------------------------------------------
here #tempTable is temporary table.
Hope this will help you, if not working please reply me []

just delete all those rows and insert the only one i don't know hope it can help