April 27, 2011 at 9:34 pm
Hi guys, I'm new to Sql Server. I have a question here....
I have a table with 6 rows
here it goes....
CatID CatName RefID
1 Lenovo 21
2 Sony 22
2 Sony 22
3 Dell 23
4 Toshiba 24
4 Toshiba 24
My question is I want to eliminate two duplicate rows from above table
2, sony,22 and 4,Toshiba, 24, How can i do that? Help is appreciated...Thanks in advance...
April 27, 2011 at 9:47 pm
Try this:
declare @table table
( catid int, catname varchar(25) , refid int )
insert into @table values
(1 ,'Lenovo', 21),
(2 ,'Sony', 22),
(2 ,'Sony', 22),
(3 ,'Dell', 23),
(4 ,'Toshiba', 24),
(4 ,'Toshiba', 24)
; with cte as
(
select * , rn = ROW_NUMBER() over( partition by catname order by catid) from @table
)
-- delete from
select *
from cte where rn = 1
April 27, 2011 at 10:14 pm
Thanks for the reply....Got it
April 28, 2011 at 1:08 am
Hello,
You can check delete duplicates rows using Row_Number() with Partition By clause
If you have identical rows in the table, you can also refer to methods mentioned at http://www.kodyaz.com/articles/delete-duplicate-records-rows-in-a-table.aspx
But the main issue here is according to which criterias you can name a column as duplicate.
April 28, 2011 at 1:39 am
Refer this URL. should help you great bit
http://www.simple-talk.com/sql/t-sql-programming/removing-duplicates-from-a-table-in-sql-server/
April 28, 2011 at 9:24 am
Thank you all
April 28, 2011 at 11:04 am
ColdCoffee (4/27/2011)
Try this:
declare @table table( catid int, catname varchar(25) , refid int )
insert into @table values
(1 ,'Lenovo', 21),
(2 ,'Sony', 22),
(2 ,'Sony', 22),
(3 ,'Dell', 23),
(4 ,'Toshiba', 24),
(4 ,'Toshiba', 24)
; with cte as
(
select * , rn = ROW_NUMBER() over( partition by catname order by catid) from @table
)
-- delete from
select *
from cte where rn = 1
When you use the DELETE FROM, shouldn't that last line read
FROM CTE WHERE rn > 1
Otherwise all you'll be left with are the rows that had duplicates and if there are more than 2 duplicates, you'll still have all but the first instance of it.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 28, 2011 at 12:07 pm
Stefan Krzywicki (4/28/2011)
ColdCoffee (4/27/2011)
Try this:
declare @table table( catid int, catname varchar(25) , refid int )
insert into @table values
(1 ,'Lenovo', 21),
(2 ,'Sony', 22),
(2 ,'Sony', 22),
(3 ,'Dell', 23),
(4 ,'Toshiba', 24),
(4 ,'Toshiba', 24)
; with cte as
(
select * , rn = ROW_NUMBER() over( partition by catname order by catid) from @table
)
-- delete from
select *
from cte where rn = 1
When you use the DELETE FROM, shouldn't that last line read
FROM CTE WHERE rn > 1
Otherwise all you'll be left with are the rows that had duplicates and if there are more than 2 duplicates, you'll still have all but the first instance of it.
Yes Stefan, you are right... i just inlcuded DELETE to let the OP know where delete fits in 🙂 and leave the "deletion" part to him...
April 28, 2011 at 12:15 pm
ColdCoffee (4/28/2011)
Stefan Krzywicki (4/28/2011)
ColdCoffee (4/27/2011)
Try this:
declare @table table( catid int, catname varchar(25) , refid int )
insert into @table values
(1 ,'Lenovo', 21),
(2 ,'Sony', 22),
(2 ,'Sony', 22),
(3 ,'Dell', 23),
(4 ,'Toshiba', 24),
(4 ,'Toshiba', 24)
; with cte as
(
select * , rn = ROW_NUMBER() over( partition by catname order by catid) from @table
)
-- delete from
select *
from cte where rn = 1
When you use the DELETE FROM, shouldn't that last line read
FROM CTE WHERE rn > 1
Otherwise all you'll be left with are the rows that had duplicates and if there are more than 2 duplicates, you'll still have all but the first instance of it.
Yes Stefan, you are right... i just inlcuded DELETE to let the OP know where delete fits in 🙂 and leave the "deletion" part to him...
Just wanted to explicitly state it 'cause that could be a disaster if the OP just uncommented DELETE FROM and commented out the SELECT
Also, you don't have to use a CTE for this, you can just use a subquery
declare @table table
( catid int, catname varchar(25) , refid int )
insert into @table values
(1 ,'Lenovo', 21),
(2 ,'Sony', 22),
(2 ,'Sony', 22),
(3 ,'Dell', 23),
(4 ,'Toshiba', 24),
(4 ,'Toshiba', 24)
DELETE X FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY catid ORDER BY catid) R, catid, catname, refid
FROM @table) X
WHERE R > 1
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 28, 2011 at 12:30 pm
Stefan Krzywicki (4/28/2011)
Also, you don't have to use a CTE for this, you can just use a subquery
Aren't CTE and SubQuery synonymous except for the fact that CTE has an extra advantage of recursion ?
April 28, 2011 at 1:04 pm
ColdCoffee (4/28/2011)
Stefan Krzywicki (4/28/2011)
Also, you don't have to use a CTE for this, you can just use a subqueryAren't CTE and SubQuery synonymous except for the fact that CTE has an extra advantage of recursion ?
I'm not sure, but in this case you don't need recursion and some people (like me) find the simple subquery to be easier to read. Doing a performance test on it and checking the job plans could be interesting though.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 28, 2011 at 1:31 pm
Stefan Krzywicki (4/28/2011)
ColdCoffee (4/28/2011)
Stefan Krzywicki (4/28/2011)
Also, you don't have to use a CTE for this, you can just use a subqueryAren't CTE and SubQuery synonymous except for the fact that CTE has an extra advantage of recursion ?
I'm not sure, but in this case you don't need recursion and some people (like me) find the simple subquery to be easier to read. Doing a performance test on it and checking the job plans could be interesting though.
I actually quite dint find any difference in the plans between subquery and CTE ( has been the case always with all my code).. i actually find the CTE more readable than subqueries 😀 difference of opinions, eh 😛
April 28, 2011 at 1:43 pm
ColdCoffee (4/28/2011)
Stefan Krzywicki (4/28/2011)
ColdCoffee (4/28/2011)
Stefan Krzywicki (4/28/2011)
Also, you don't have to use a CTE for this, you can just use a subqueryAren't CTE and SubQuery synonymous except for the fact that CTE has an extra advantage of recursion ?
I'm not sure, but in this case you don't need recursion and some people (like me) find the simple subquery to be easier to read. Doing a performance test on it and checking the job plans could be interesting though.
I actually quite dint find any difference in the plans between subquery and CTE ( has been the case always with all my code).. i actually find the CTE more readable than subqueries 😀 difference of opinions, eh 😛
Yep, as soon as it comes down to "readable" it is always a matter of opinion. : -)
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 29, 2011 at 1:47 pm
So which one works better when i have huge data? CTE or Subquery??
April 29, 2011 at 2:12 pm
sqlsree (4/29/2011)
So which one works better when i have huge data? CTE or Subquery??
According to ColdCoffee, it shouldn't make a difference which you use. Use whichever feels more usable/understandable to you.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply