Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Consolidating records - TSQL problem Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, September 25, 2012 6:48 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 8:27 AM Points: 6,286, Visits: 12,103
 Abu Dina (9/25/2012)Lynne's solution works if we assume that the retained ID is always the maximum.But tthe solution doesn't work with the following record set:`drop table dbo.testing create table dbo.testing (retained int, dropped int)insert into dbo.testing (retained, dropped)select 767884, 157441 union allselect 1046261, 157441 union allselect 6699, 157441 union allselect 157441, 73635 union allselect 767884, 73635 union allselect 1046261, 73635 union allselect 6699, 73635 union allselect 1046261, 767884 union allselect 6699, 767884 union allselect 6699, 1046261`I will keep trying to see if I can come up with a solution but if anyone else can think of something then that'd be great!Thanks in advance.Yep...`;WITH retained AS ( SELECT t.* FROM testing t WHERE NOT EXISTS (SELECT 1 FROM testing i WHERE i.dropped = t.retained))SELECT [Level] = 1, l1.retained, l1.droppedFROM retained l1UNION ALLSELECT [Level] = 2, l1.retained, l2.retainedFROM retained l1inner JOIN testing l2 ON l2.retained = l1.droppedUNION ALLSELECT [Level] = 3, l1.retained, l3.retainedFROM retained l1inner JOIN testing l2 ON l2.retained = l1.droppedinner JOIN testing l3 ON l3.retained = l2.droppedUNION ALLSELECT [Level] = 4, l1.retained, l3.droppedFROM retained l1inner JOIN testing l2 ON l2.retained = l1.droppedinner JOIN testing l3 ON l3.retained = l2.dropped` “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps
Post #1364006
 Posted Tuesday, September 25, 2012 7:18 AM
 SSChasing Mays Group: General Forum Members Last Login: Yesterday @ 7:44 AM Points: 620, Visits: 2,928
 Thanks Chris,I'm looking at recursive CTEs but I don't think it will work. Well I've been trying for the last couple of hours and it's still not giving the same result as my original update (which I run multiple times until 0 rows are updated!).Thanks for your efforts. ---------------------------------------------------------It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda. David Edwards - Media lensSociety has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.Howard Zinn
Post #1364024
 Posted Tuesday, September 25, 2012 7:46 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 8:27 AM Points: 6,286, Visits: 12,103
 Abu Dina (9/25/2012)Thanks Chris,I'm looking at recursive CTEs but I don't think it will work. Well I've been trying for the last couple of hours and it's still not giving the same result as my original update (which I run multiple times until 0 rows are updated!).Thanks for your efforts.You're welcome.I tried a rCTE too, and although it does resolve the hierarchy, I couldn't get it to come out in the required format - it would take quite a bit of post-processing which would have made it unfeasibly slow. “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps
Post #1364043
 Posted Tuesday, September 25, 2012 9:43 AM
 SSChasing Mays Group: General Forum Members Last Login: Yesterday @ 7:44 AM Points: 620, Visits: 2,928
 Hi Chris,Here is my attempt doing this with a rCTE`;WITH retained AS (SELECT t.retained, dropped FROM testing t WHERE NOT EXISTS (SELECT 1 FROM testing i WHERE i.dropped = t.retained) UNION ALL SELECT b.retained, t1.dropped FROM testing AS t1 INNER JOIN retained as b ON t1.retained = b.dropped) UPDATE t SET t.retained = uv.retained FROM testing t INNER JOIN retained uv ON t.dropped = uv.dropped`This works with the sample data but something isn't quite right with this. Tried it with a sample of 160000 records, it took 17 seconds.When I increased the sample record set to 200000, the query just keeps going. It's scaling up badly or there's some dodgy data that the above logic just can't handle efficiently. This is what the estimated execution plan is telling me:Tried adding a couple of indexes on retained and dropped columns but that made no difference to the estimated execution plan.Arrrggghhh! ---------------------------------------------------------It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda. David Edwards - Media lensSociety has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.Howard Zinn
Post #1364142
 Posted Tuesday, September 25, 2012 11:26 AM
 SSCommitted Group: General Forum Members Last Login: Today @ 9:06 AM Points: 1,897, Visits: 4,204
 I know it could be a bad solution, and maybe you had tought about it.But since the rCTE is not scaling well (and it sure won't) why don't you use your original solution in a while loop for @@rowcount > 0?I'm not sure which will scale better, but it's still an option. Luis C.Please don't trust me, test the solutions I give you before using them.Forum Etiquette: How to post data/code on a forum to get the best help
Post #1364194
 Posted Tuesday, September 25, 2012 1:04 PM
 SSC-Insane Group: General Forum Members Last Login: Today @ 9:01 AM Points: 22,096, Visits: 29,030
 Lynn Pettis (9/25/2012)Abu Dina (9/25/2012)Lynne's solution works if we assume that the retained ID is always the maximum.But tthe solution doesn't work with the following record set:`drop table dbo.testing create table dbo.testing (retained int, dropped int)insert into dbo.testing (retained, dropped)select 767884, 157441 union allselect 1046261, 157441 union allselect 6699, 157441 union allselect 157441, 73635 union allselect 767884, 73635 union allselect 1046261, 73635 union allselect 6699, 73635 union allselect 1046261, 767884 union allselect 6699, 767884 union allselect 6699, 1046261`I will keep trying to see if I can come up with a solution but if anyone else can think of something then that'd be great!Thanks in advance.I can only write code based on what you provided. Based on the sample data and expected results, what I saw was the max id being retained. With the new data, what are the rules for determining what ID is used? Also, you posted additional data but not additional expected results.Okay, let me try this sgain from my phone while eating lunch at McDonalds.Try this, I wrote it on a napkin while eating and looking at the data you posted.`with rCTE as (select t1.retained, t1.dropped, t1.retained as TopLevelfrom dbo.testing t1 left outer join dbo.testing t2 on t1.retained = t2.droppedwhere t2.dropped is nullunion allselect t1.retained, t1.dropped, r.TopLevelfrom dbo.testing t1inner join rCTE r on t1.retained = r.dropped)update tu set retained = r.TopLevelfrom dbo.testing tu inner join rCTE r on tu.retained = r.retained and tu.dropped = r.dropped;`Let me know if it works. I can't test it on my phone.
Post #1364245
 Posted Tuesday, September 25, 2012 2:25 PM
 SSChasing Mays Group: General Forum Members Last Login: Yesterday @ 7:44 AM Points: 620, Visits: 2,928
 Luis Cazares (9/25/2012)I know it could be a bad solution, and maybe you had tought about it.But since the rCTE is not scaling well (and it sure won't) why don't you use your original solution in a while loop for @@rowcount > 0?I'm not sure which will scale better, but it's still an option.Thanks Luis, your suggestion is valid. I see that Lynne has come back with a potential solution. I will test tomorrow and report back.Regards. ---------------------------------------------------------It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda. David Edwards - Media lensSociety has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.Howard Zinn
Post #1364286
 Posted Tuesday, September 25, 2012 11:54 PM
 SSC-Insane Group: General Forum Members Last Login: Today @ 9:01 AM Points: 22,096, Visits: 29,030
 Here is my code, reformatted the way I like to it.`with rCTE as (select t1.retained, t1.dropped, t1.retained as TopLevelfrom dbo.testing t1 left outer join dbo.testing t2 on t1.retained = t2.droppedwhere t2.dropped is nullunion allselect t1.retained, t1.dropped, r.TopLevelfrom dbo.testing t1 inner join rCTE r on t1.retained = r.dropped)update tu set retained = r.TopLevelfrom dbo.testing tu inner join rCTE r on tu.retained = r.retained and tu.dropped = r.dropped;`
Post #1364443
 Posted Wednesday, September 26, 2012 12:07 AM
 SSC-Insane Group: General Forum Members Last Login: Today @ 9:01 AM Points: 22,096, Visits: 29,030
 Abu Dina (9/25/2012)Luis Cazares (9/25/2012)I know it could be a bad solution, and maybe you had tought about it.But since the rCTE is not scaling well (and it sure won't) why don't you use your original solution in a while loop for @@rowcount > 0?I'm not sure which will scale better, but it's still an option.Thanks Luis, your suggestion is valid. I see that Lynne has come back with a potential solution. I will test tomorrow and report back.Regards.By the way, no 'e' at the end of Lynn.
Post #1364447
 Posted Wednesday, September 26, 2012 2:21 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 8:27 AM Points: 6,286, Visits: 12,103
 Lynn Pettis (9/25/2012)Here is my code, reformatted the way I like to it.`with rCTE as (select t1.retained, t1.dropped, t1.retained as TopLevelfrom dbo.testing t1 left outer join dbo.testing t2 on t1.retained = t2.droppedwhere t2.dropped is nullunion allselect t1.retained, t1.dropped, r.TopLevelfrom dbo.testing t1 inner join rCTE r on t1.retained = r.dropped)update tu set retained = r.TopLevelfrom dbo.testing tu inner join rCTE r on tu.retained = r.retained and tu.dropped = r.dropped;`Nice one, Lynn. I was working on something very similar which includes "evidence" in the data that it works;`;WITH rCTE AS ( SELECT [level] = 1, t1.retained, t1.dropped, --fullchain = CAST(CAST(t1.retained AS VARCHAR(10)) + ',' + CAST(t1.dropped AS VARCHAR(10)) AS VARCHAR(8000)), Toplevel = t1.retained FROM dbo.testing t1 LEFT OUTER JOIN dbo.testing t2 ON t1.retained = t2.dropped WHERE t2.dropped IS NULL UNION ALL SELECT [level] = [level] + 1, t1.retained, t1.dropped, --fullchain = r.fullchain + ',' + CAST(t1.dropped AS VARCHAR(10)), Toplevel = r.Toplevel FROM dbo.testing t1 INNER JOIN rCTE r ON t1.retained = r.dropped)--SELECT * --FROM rCTE--ORDER BY level, droppedUPDATE tu SET retained = r.TopLevelFROM dbo.testing tu INNER JOIN rCTE r ON tu.dropped = r.dropped;SELECT * FROM dbo.testing`Where evidence is the column [fullchain]. “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps
Post #1364501

 Permissions