sql server - Show each row that is a duplicate -
i have table contains duplicate rows.for ex table
originalurl newurl /blog /blog es/blog es/blog blog blog now duplicates follows
output:
originalurl newurl /blog /blog blog blog thanks "zohar peled" have achieved of through code http://sqlfiddle.com/#!6/c96cc/5.
but when add blog without (/) duplicate shown above.but isn't happening code.so me achieve this
final update
after yet goal post shifting, i've updated cte again.
final update since if going change demands again i've had enough.
please take advice future questions:
- defind problem can.
- provide accurate table structure , sample data ddl+dml.
don't link sqlfiddle suffers lot of downtime. - provide accurate expected output
- show efforts solve problem.
here last updated cte, else remains in last update. sqlfiddle here.
;with cte ( select originalurl, newurl, right(originalurl, len(originalurl) - charindex('/', originalurl, case when patindex('%://%/%', originalurl) > 0 patindex('%://%/%', originalurl)+3 else 0 end )+1 ) product mytable ) update
updated sample data (again....)
-- create sample table , data. -- ddl create table mytable ( originalurl varchar(50), newurl varchar(50) ) -- dml insert mytable values ('/blog', '/blog'), ('http://gaming.corsair.com/blog', 'http://gaming.corsair.com/blog'), ('http://www.corsair.com/blog', 'http://www.corsair.com/blog'), ('http://www.corsair.com/es/blog', 'http://www.corsair.com/es/blog') i've updated cte include in 'product' last '/' char, product first 3 rows /blog, , last 1 it's /es/blog. after protocol , domain parts of url considered product now. note - should apply https , other protocol.
;with cte ( select originalurl, newurl, right(originalurl, len(originalurl) - charindex('/', originalurl, patindex('%://%', originalurl)+3 -- location right after '://' )+1 -- location right after first / after patindex ) product mytable ) my query on new cte remain in last attempt:
select t1.originalurl, t1.newurl,t2.product cte t1 inner join ( select product cte group product having count(1) > 1 ) t2 on(t1.product = t2.product) here output:
originalurl newurl product --------------------------------------------------------------------------- /blog /blog /blog http://gaming.corsair.com/blog http://gaming.corsair.com/blog /blog http://www.corsair.com/blog http://www.corsair.com/blog /blog earlier versions
so, duplicates mean rows have same string after last / in originalurl column?
if so, might want try this:
-- create sample table , data. -- ddl create table mytable ( originalurl varchar(50), newurl varchar(50) ) -- dml insert mytable values ('/blog', '/en-us/blog'), ('/blog', '/en-us/blog'), ('http://www.corsair.com/blog', 'http://www.corsair.com/blog'), ('http://gaming.corsair.com/blog', 'http://gaming.corsair.com/blog'), ('blablabla/blog', 'yadayada/blog'), ('i don''t see wrong this/answer', 'it seems working/fine'), ('unless my/answer', 'assumes duplicates else then/you'), ('300r', '300r') note: should use way provide sample data when asking questions sql.
way can copy ddl + dml sqlfiddle or our own environments , test answers give.
i've used cte insulate last word after last / in sample data, have write right expression once.
;with cte ( select originalurl, newurl, right(originalurl, case when charindex('/', originalurl) > 0 charindex('/', reverse(originalurl))-1 else len(originalurl) end) product mytable ) select distinct t1.originalurl, t1.newurl,t1.product cte t1 inner join cte t2 on(t1.product = t2.product) t1.originalurl <> t2.originalurl update
using same cte, try this:
select t1.originalurl, t1.newurl,t2.product cte t1 inner join ( select product cte group product having count(1) > 1 ) t2 on(t1.product = t2.product) results:
originalurl newurl product -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- don't see wrong this/answer seems working/fine answer unless my/answer assumes duplicates else then/you answer /blog /en-us/blog blog /blog /en-us/blog blog http://www.corsair.com/blog http://www.corsair.com/blog blog http://gaming.corsair.com/blog http://gaming.corsair.com/blog blog blablabla/blog yadayada/blog blog
Comments
Post a Comment