how to loop without endless joins

Question

how to loop without endless joins

astrid 69000

Hall of Fame

Points: 3208
More actions
February 24, 2016 at 5:30 am

#316252

Hi,
I have a table that each row related to another row, and apart from that they have nothing in common.
create table #mynumbers (Appl int, Pre_App int);
insert into #mynumbers (Appl, Pre_App) values (58672235, 0);
insert into #mynumbers (Appl, Pre_App) values (58791134, 58672235);
insert into #mynumbers (Appl, Pre_App) values (58800760, 58791134);
insert into #mynumbers (Appl, Pre_App) values (58993700, 0);
insert into #mynumbers (Appl, Pre_App) values (59068028, 58993700);
insert into #mynumbers (Appl, Pre_App) values (59139976, 59068028);
insert into #mynumbers (Appl, Pre_App) values (59200408, 59139976);
select * from #mynumbers
I would like to query it in a way that a column will be added with the appl number. that number is the one that the pre_app = 0
in this case the original appl would be 58672235 and 58993700.
how can i loop the data?
thanks
astrid

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 22999 More actions · Answer 1

astrid 69000 (2/24/2016)
Hi,
I have a table that each row related to another row, and apart from that they have nothing in common.
create table #mynumbers (Appl int, Pre_App int);
insert into #mynumbers (Appl, Pre_App) values (58672235, 0);
insert into #mynumbers (Appl, Pre_App) values (58791134, 58672235);
insert into #mynumbers (Appl, Pre_App) values (58800760, 58791134);
insert into #mynumbers (Appl, Pre_App) values (58993700, 0);
insert into #mynumbers (Appl, Pre_App) values (59068028, 58993700);
insert into #mynumbers (Appl, Pre_App) values (59139976, 59068028);
insert into #mynumbers (Appl, Pre_App) values (59200408, 59139976);
select * from #mynumbers
I would like to query it in a way that a column will be added with the appl number. that number is the one that the pre_app = 0
in this case the original appl would be 58672235 and 58993700.
how can i loop the data?
thanks
astrid

I'm really not sure what you mean here but this link[/url] will give you some excellent advice about how to avoid a loop.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 2

thanks, i will check the link.

but basically what i meant is that my final result will look like my final result table.

create table #mynumbers (Appl int, Pre_App int);

insert into #mynumbers (Appl, Pre_App) values (58672235, 0);

insert into #mynumbers (Appl, Pre_App) values (58791134, 58672235);

insert into #mynumbers (Appl, Pre_App) values (58800760, 58791134);

insert into #mynumbers (Appl, Pre_App) values (58993700, 0);

insert into #mynumbers (Appl, Pre_App) values (59068028, 58993700);

insert into #mynumbers (Appl, Pre_App) values (59139976, 59068028);

insert into #mynumbers (Appl, Pre_App) values (59200408, 59139976);

select * from #mynumbers

create table #finalresult (Appl int, Pre_App int, Original_Appl int);

insert into #finalresult (Appl, Pre_App) values (58672235, 0, 58672235);

insert into #finalresult (Appl, Pre_App) values (58791134, 58672235, 58672235);

insert into #finalresult (Appl, Pre_App) values (58800760, 58791134, 58672235);

insert into #finalresult (Appl, Pre_App) values (58993700, 0, 58993700);

insert into #finalresult (Appl, Pre_App) values (59068028, 58993700, 58993700);

insert into #finalresult (Appl, Pre_App) values (59139976, 59068028, 58993700);

insert into #finalresult (Appl, Pre_App) values (59200408, 59139976, 58993700);

select * from #finalresult

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

I don't understand the logic here. Can you try to explain what you are trying to do?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Does this article work for you?

http://www.sqlservercentral.com/articles/set-based+loop/127670/

I could give you an answer, but I already wrote the whole explanation in there. 😀

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 5

thanks, i will read the article.

and there is no logic, whoever wrote that db, didnt have it together.

basically i want to join the appl that are together and on the table you need to look for the previous one till you hit 0.

:w00t::w00t::w00t:

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

How about this?

with rcte as (

select

mn.Appl,

mn.Pre_App,

mn.Appl as Original_Appl,

SortKey = 0

from

#mynumbers mn

where

Pre_App = 0

union all

select

mn.Appl,

mn.Pre_App,

r.Original_Appl,

SortKey = r.SortKey + 1

from

#mynumbers mn

inner join rcte r

on mn.Pre_App = r.Appl

)

select

Appl,

Pre_App,

Original_Appl

from

rcte

order by

Original_Appl,

SortKey;

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Lynn Pettis (2/24/2016)
How about this?

The set-based loop might be faster. The example shown in the article is basically the same as this one and the improvement was noticeable.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Luis Cazares (2/24/2016)
Lynn Pettis (2/24/2016)
How about this?
The set-based loop might be faster. The example shown in the article is basically the same as this one and the improvement was noticeable.

Haven't looked at the article, just went off what looks like a hierarchy which recursive CTEs are usually good at. Maybe not this one, but I do use this method for our hierarchical lookups where I work.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

Lynn Pettis (2/24/2016)
Luis Cazares (2/24/2016)
Lynn Pettis (2/24/2016)
How about this?
The set-based loop might be faster. The example shown in the article is basically the same as this one and the improvement was noticeable.
Haven't looked at the article, just went off what looks like a hierarchy which recursive CTEs are usually good at. Maybe not this one, but I do use this method for our hierarchical lookups where I work.

Don't get me wrong, recursive CTEs are a great tool for hierarchies. It's my option when selecting a portion of a hierarchy. But when a full hierarchy needs to be traversed, rCTEs start to fail and might be even slower than pure RBAR with a nested loop.

I still need to get a better understanding on how to calculate when's the best option for one or the other, to be able to share it. Meanwhile, the best option is to test all possibilities.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Ed Wagner SSC Guru Points: 287026 More actions · Answer 10

I'm not sure if I'm reading this right, but it looks like you want to autofill a new column with the last Appl resetting whenever pre_app = 0.

If this is the case, you can adapt a quirky update to do it. Check out http://www.sqlservercentral.com/articles/T-SQL/68467/ where Jeff described a quirky update. Just be careful to obey the rules of the game - they're important.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Could also look at using the windowing functions since this is posted in SQL Server 2014.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 12

Sean Lange (2/24/2016)
I don't understand the logic here. Can you try to explain what you are trying to do?

It's an adjacency list and he simply wants to display the adjacency list with the top level for each tree in the forest to be listed in the 3rd column.

Sean Lange (2/24/2016)
I don't understand the logic here. Can you try to explain what you are trying to do?

It's an adjacency list and he simply wants to display the adjacency list with the top level for each tree in the forest to be listed in the 3rd column.

Appl is the "Child", Pre_App is the "Parent", and Original_Appl is the ID of the Child at Level 1 (assuming that "0" is NOT considered to be Level 1").

It's a hierarchy and a simple "data smear" won't work here because there's nothing to guarantee the order.

I'll be back.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

First of all, apologies to Lynn Pettis. He's spot on for the solution to this problem but I missed looking at his code. Nice job for realizing that 1) this was a hierarchical problem and 2) realizing that a "Sort Key" is a must. Unfortunately, if you add another Level 3 and 4 with the "right" numbering for the Appl, the new Level 4 doesn't follow the correct hierarchical order according to the parent/child relationship in the hierarchy.

Here's the code with the added nodes.

create table #mynumbers (Appl int, Pre_App int);

insert into #mynumbers (Appl, Pre_App) values (58672235, 0);

insert into #mynumbers (Appl, Pre_App) values (58791134, 58672235);

insert into #mynumbers (Appl, Pre_App) values (58800760, 58791134);

insert into #mynumbers (Appl, Pre_App) values (58800761, 58791134); --Added as another level 3

insert into #mynumbers (Appl, Pre_App) values (58800000, 58800760); --Added as a level 4, ends in wrong place for Lynn's

insert into #mynumbers (Appl, Pre_App) values (58993700, 0);

insert into #mynumbers (Appl, Pre_App) values (59068028, 58993700);

insert into #mynumbers (Appl, Pre_App) values (59139976, 59068028);

insert into #mynumbers (Appl, Pre_App) values (59200408, 59139976);

The code below works in a similar manner except I store the "child" ID as a concatenated binary path, which solves the correct order problem and sets us up for some other magic that I'll explain in a minute. I also threw in the "Hierarchical Level" because 1) almost everyone eventually wants to know the value and 2) it's also important to that other magic. Here's the code.

WITH cteTraverse AS

( --=== Find the root nodes and use them for the start of the SortPath

SELECT Appl

,Pre_App

,hLevel = 1

,SortPath = CAST(CAST(Appl AS BINARY(4)) AS VARBINARY(1000))

FROM #mynumbers

WHERE Pre_App = 0

UNION ALL

--==== Continue the traversal of the hierarchy and concatenate each level to the previous in SortPath

SELECT tbl.Appl

,tbl.Pre_App

,hLevel = cte.hLevel+1

,SortPath = CAST(cte.SortPath + CAST(tbl.Appl AS BINARY(4)) AS VARBINARY(1000))

FROM cteTraverse AS cte

JOIN #mynumbers AS tbl

ON tbl.Pre_App = cte.Appl

)

SELECT *

,Original_Appl = CAST(SUBSTRING(SortPath,1,4) AS INT) --Extracts the IDs at Level 1

FROM cteTraverse

ORDER BY SortPath

;

Of course, you can remove the SortPath and Hierarchical Level columns from the final SELECT list to meet the original requirements but let's get to the "magic"...

Most hierarchies change rather infrequently but are read from a lot. Rather than constantly and unnecessarily burning clock cycles and extra read re-traversing the hierarchy every time you want to read the hierarchy, you can build "Nested Sets" which afford very high speed, low resource usage methods for returning hierarchical data. Now, don't be fooled into getting rid of the original "Adjacency List" (parent/child structure) because Adjacency Lists are super easy to maintain. We'll just rebuild the Nested Sets when there's a change.

I'll also tell you not to be fooled into using a bloody "push stack" method, which requires an ID stack, a bazillion reads, and a slow While Loop that needs to do too much for it's own good. Instead, please see the method explained in the following article.

http://www.sqlservercentral.com/articles/Hierarchy/94040/

Now, if this hierarchy needs to be aggregated for dollar amounts or what have you (obviously, can't tell from the data given), then you need to see the following article, as well.

http://www.sqlservercentral.com/articles/T-SQL/94570/

Last but not least, hat's off to astrid 69000 for posting readily consumable data. It saves us a whole lot of time and makes things instantly clear. Well done!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 14

thanks for all the reply. now i will sit and read everything slowly :blush: