November 25, 2009 at 11:44 pm
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
November 26, 2009 at 12:10 am
See if this helps you:
November 26, 2009 at 9:42 am
hi use the below query.
WITH DUPE AS
(
SELECT BOOKNAME,PRICE,AUTHOR,ROW_NUMBER() OVER(ORDER BY BOOKNAME,PRICE,AUTHOR ) AS ROWNUMBER
FROM TABLENMAE )
DELETE FROM DUPE
WHERE ROWNUMBER NOT IN (SELECT MAX(ROWNUMBER) FROM DUPE GROUP BY BOOKNMAE,PICE,AUTHOR
)
Regards,
MC
Thanks & Regards,
MC
November 26, 2009 at 10:51 am
ashok
CREATE TABLE #Books(BookName VARCHAR(20), Price MONEY, Author VARCHAR(50))
INSERT INTO #Books
SELECT 'C++', 150, 'kanithkar' UNION ALL
SELECT 'C++', 150, 'kanithkar' UNION ALL
SELECT 'C#', 150, 'kanithkar' UNION ALL
SELECT 'Net', 250, 'smith' UNION ALL
SELECT 'Net', 250, 'jones' UNION ALL
SELECT 'SQL', 250, 'jack'
;with numbered as(SELECT rowno=row_number() over
(partition by Bookname, Author order by Bookname),Bookname,Author,Price from #Books)
--For testing use:
SELECT * FROM numbered --To check if output is correct
--Once tested and output is correct replace above statement with
DELETE from numbered WHERE rowno > 1
My select output:
rownoBookname AuthorPrice
1C# kanithkar150.00
1C++ kanithkar150.00
2C++ kanithkar150.00
1Net jones 250.00
1Net smith 250.00
1SQL jack 250.00
Changed T-SQL after testing to use DELETE. Resuls:
(1 row(s) affected)
BookName Price Author
C++ 150.00kanithkar
C# 150.00kanithkar
Net 250.00smith
Net 250.00jones
SQL 250.00jack
November 27, 2009 at 9:02 am
bitbucket has the better solution.
also, may I recommend you add a primary key to your table(s)??
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 27, 2009 at 11:16 am
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy