get sorted data

Hi,
I have a table containing the following data:
ID TitelD fk_Next_Knoten_ID fk_Previous_Knoten_ID
-- -- -- --
131 Kupfer, Erde 138 0
132 Flachkabel 0 141
138 Dr=E4hte, Seil 139 131
139 Niederspannu 140 138
140 Sicherheitsk 141 139
141 Litzen 132 140
How can I select the data in the correct order:
131 Kupfer, Erde 138 0
138 Dr=E4hte, Seil 139 131
139 Niederspannu 140 138
140 Sicherheitsk 141 139
141 Litzen 132 140
132 Flachkabel 0 141
Does anybody has an idea? Maybe with the help of a cursor?
Thanks,
MarcHave you tried ORDER BY clause?
<glompf@.gmail.com> wrote in message
news:1128951956.866049.261510@.o13g2000cwo.googlegroups.com...
Hi,
I have a table containing the following data:
ID TitelD fk_Next_Knoten_ID fk_Previous_Knoten_ID
-- -- -- --
131 Kupfer, Erde 138 0
132 Flachkabel 0 141
138 Drhte, Seil 139 131
139 Niederspannu 140 138
140 Sicherheitsk 141 139
141 Litzen 132 140
How can I select the data in the correct order:
131 Kupfer, Erde 138 0
138 Drhte, Seil 139 131
139 Niederspannu 140 138
140 Sicherheitsk 141 139
141 Litzen 132 140
132 Flachkabel 0 141
Does anybody has an idea? Maybe with the help of a cursor?
Thanks,
Marc|||What would you use as the order by parameter?
Marc|||Try,
...
order by fk_Previous_Knoten_ID asc
AMB
"glompf@.gmail.com" wrote:

> Hi,
> I have a table containing the following data:
> ID TitelD fk_Next_Knoten_ID fk_Previous_Knoten_ID
> -- -- -- --
> 131 Kupfer, Erde 138 0
> 132 Flachkabel 0 141
> 138 Dr?hte, Seil 139 131
> 139 Niederspannu 140 138
> 140 Sicherheitsk 141 139
> 141 Litzen 132 140
> How can I select the data in the correct order:
> 131 Kupfer, Erde 138 0
> 138 Dr?hte, Seil 139 131
> 139 Niederspannu 140 138
> 140 Sicherheitsk 141 139
> 141 Litzen 132 140
> 132 Flachkabel 0 141
> Does anybody has an idea? Maybe with the help of a cursor?
> Thanks,
> Marc
>|||If you are able to change this design then I suggest you do. Having
both next and previous IDs in the same table looks redundant. If you
can't fix the design then you may have to use a cursor.
If there is some fixed maximum depth to the hierarchy then you may be
able to achieve the same with self-joins. To answer this fully I'd like
to know if there are any branches or loops in the hierarchy. Are
Previous and Next unique? Please post DDL so that we don't have to
guess.
David Portas
SQL Server MVP
--|||create table #test
(
col1 int not null primary key,
col2 char(1),
col3 int not null
)
insert into #test values (1,'a',0)
insert into #test values (2,'b',100)
insert into #test values (3,'c',80)
insert into #test values (4,'d',90)
insert into #test values (5,'e',92)
insert into #test values (6,'f',99)
select * from #test order by col3 asc
<glompf@.gmail.com> wrote in message
news:1128953061.383573.43240@.o13g2000cwo.googlegroups.com...
> What would you use as the order by parameter?
> Marc
>|||Hi David,
Between the same TLID (=3D additional hierarchical level) the next and
prev are unique
The 'real' data looks like this:
create table #tl
(
ID int,
TLID int,
TitelD varchar(50),
fk_Next_Knoten_ID int,
fk_Previous_Knoten_ID int)
)
insert into #tl values (131,21,'Kupfer, Erder ',138,0 )
insert into #tl values (132,21,'Flachkabel ',147,141)
insert into #tl values (133,21,'Anschluss-/Verl=E4nger',134,146)
insert into #tl values (134,21,'Koaxialkabel ',136,133)
insert into #tl values (135,21,'Datenkabel ',145,137)
insert into #tl values (136,21,'Kabel T+T ',137,134)
insert into #tl values (137,21,'Dr=E4hte T+T ',135,136)
insert into #tl values (138,21,'Dr=E4hte, Seile ',139,131)
insert into #tl values (139,21,'Niederspannungskabel',140,138)
insert into #tl values (140,21,'Sicherheitskabel ',141,139)
insert into #tl values (141,21,'Litzen ',132,140)
insert into #tl values (142,21,'Steuerkabel ',143,147)
insert into #tl values (143,21,'Apparatekabel ',148,142)
insert into #tl values (144,21,'Kabel, konfektionier',146,149)
insert into #tl values (145,21,'Heizb=E4nder/-Kabel/-M',0 ,135)
insert into #tl values (146,21,'Schwachstromleiter ',133,144)
insert into #tl values (147,21,'Hochspannungskabel, ',142,132)
insert into #tl values (148,21,'Silikonkabel <Gd>, w',149,143)
insert into #tl values (149,21,'Gummischn=FCre, flexib',144,148)
insert into #tl values (150,22,'Kabelbriden ',151,0 )
insert into #tl values (151,22,'Kabelbinder, Spiralb',152,150)
insert into #tl values (152,22,'Leitungs-Schnellverl',153,151)
insert into #tl values (153,22,'Kabelendt=FCllen, Knic',159,152)
insert into #tl values (154,22,'Kabelarmaturen ',160,159)
insert into #tl values (155,22,'Kabelverschraubungen',156,157)
insert into #tl values (156,22,'EMP-Material ',158,155)
insert into #tl values (157,22,'Kabeldurchf=FChrungen ',155,160)
insert into #tl values (158,22,'Freileitungsmaterial',0 ,156)
insert into #tl values (159,22,'Schrumpfschl=E4uche ',154,153)
insert into #tl values (160,22,'Abdichtelemente ',157,154)
insert into #tl values (161,23,'Installationsrohre ',162,0 )
insert into #tl values (162,23,'Schl=E4uche, Cu-Wellro',171,161)
insert into #tl values (163,23,'Briden, Klemmbriden,',173,171)
insert into #tl values (164,23,'Schalungsmuffen ',168,167)
insert into #tl values (165,23,'Schlauchzubeh=F6r ',166,173)
insert into #tl values (166,23,'Reduktionen, Erweite',167,165)
insert into #tl values (167,23,'Dichtungszapfen ',164,166)
insert into #tl values (168,23,'Schalungsschoner ',172,164)
insert into #tl values (169,23,'Kabelkan=E4le ',170,172)
insert into #tl values (170,23,'Gummibodenleisten ',0 ,169)
insert into #tl values (171,23,'Kabelschutzrohre und',163,162)
insert into #tl values (172,23,'Rohrst=FCtzen ',169,168)
insert into #tl values (173,23,'Rohrzubeh=F6r ',165,163)
insert into #tl values (174,24,'AP-Abzweigdosen ',175,0 )
insert into #tl values (175,24,'Flachkabel-Abzweigdo',189,174)
insert into #tl values (176,24,'Anschlussdosen ',177,189)
insert into #tl values (177,24,'Bodendosen ',178,176)
insert into #tl values (178,24,'Anschlusss=E4ulen/Bohr',179,177)
insert into #tl values (179,24,'Leiterschienen ',180,178)
insert into #tl values (180,24,'UP-Abzweigdosen ',181,179)
insert into #tl values (181,24,'UP-Schalungskasten ',182,180)
insert into #tl values (182,24,'Verbindungs-/Abzweig',184,181)
insert into #tl values (183,24,'Klemmen ',188,187)
insert into #tl values (184,24,'Kabelschuhe/Konusver',185,182)
insert into #tl values (185,24,'Aderendh=FClsen ',186,184)
insert into #tl values (186,24,'Sammelschienenmateri',187,185)
insert into #tl values (187,24,'Erdungsmaterial ',183,186)
insert into #tl values (188,24,'Reihenklemmen und Zu',0 ,183)
insert into #tl values (189,24,'Rundkabel-Abzweigdos',176,175)
insert into #tl values (190,25,'Schrauben ',199,0 )
insert into #tl values (191,25,'D=FCbel, Befestigungsm',192,199)
insert into #tl values (192,25,'Abschlussrosetten ',193,191)
insert into #tl values (193,25,'Profile/Profilschien',194,192)
insert into #tl values (194,25,'Norm-Bauteile ',195,193)
insert into #tl values (195,25,'Isolier-/Dichtungsma',196,194)
insert into #tl values (196,25,'Isolierplatten ',197,195)
insert into #tl values (197,25,'Bezeichnungsmaterial',198,196)
insert into #tl values (198,25,'Warnungstafeln/Warnu',0 ,197)
insert into #tl values (199,25,'L=F6tmaterial/Klebstof',191,190)
insert into #tl values (200,26,'AP-Apparate trocken ',201,0 )
insert into #tl values (201,26,'AP-Apparate feucht ',202,200)
insert into #tl values (202,26,'AP-Apparate nass ',203,201)
insert into #tl values (203,26,'AP-Apparate explosio',204,202)
insert into #tl values (204,26,'Fusschalter/-taster,',206,203)
insert into #tl values (205,26,'Schnur-Zwischenschal',0 ,206)
insert into #tl values (206,26,'Endtaster/Endschalte',205,204)
insert into #tl values (207,27,'Einlassk=E4sten ',208,0 )
insert into #tl values (208,27,'Abdeckungen/Blindabd',209,207)
insert into #tl values (209,27,'Befestigungsplatten ',210,208)
insert into #tl values (210,27,'AP-Rahmen ',211,209)
insert into #tl values (211,27,'UP-Apparate trocken ',212,210)
insert into #tl values (212,27,'UP-Apparate feucht ',213,211)
insert into #tl values (213,27,'UP-Apparate nass ',0 ,212)
insert into #tl values (214,28,'EB-Apparate ',215,0 )
insert into #tl values (215,28,'EB-Apparate feucht ',217,214)
insert into #tl values (216,28,'EB-Befehls- und Meld',0 ,218)
insert into #tl values (217,28,'EB-Apparate nass ',218,215)
insert into #tl values (218,28,'EB-Lasttrennschalter',216,217)
insert into #tl values (219,29,'Leistungs-/Motorschu',238,0 )
insert into #tl values (220,29,'Relais ',221,237)
insert into #tl values (221,29,'Sch=FCtze/Hilfssch=FCtze',222,220)
insert into #tl values (222,29,'Softstart f=FCr Drehst',244,221)
insert into #tl values (223,29,'Zeitschalter ',224,246)
insert into #tl values (224,29,'Verz=F6gerungs-/Ventil',239,223)
insert into #tl values (225,29,'Vorwahlz=E4hler ',241,239)
insert into #tl values (226,29,'Schaltuhren/Steckdos',227,240)
insert into #tl values (227,29,'D=E4mmerungsschalter ',228,226)
insert into #tl values (228,29,'Pr=E4senzmelder/Bewegu',229,227)
insert into #tl values (229,29,'Lichtschranken/Konta',230,228)
insert into #tl values (230,29,'Thermostaten/Hygrost',231,229)
insert into #tl values (231,29,'Druck-/Niveauregler ',232,230)
insert into #tl values (232,29,'=DCberwachungsger=E4te/B',243,231)
insert into #tl values (233,29,'Netzfreischalter ',0 ,234)
insert into #tl values (234,29,'Kondensatoren ',233,236)
insert into #tl values (235,29,'Schrittschalter ',237,238)
insert into #tl values (236,29,'Lastabwurfrelais ',234,245)
insert into #tl values (237,29,'Treppenhausautomaten',220,235)
insert into #tl values (238,29,'Leistungsschalter ',235,219)
insert into #tl values (239,29,'Betriebsstundenz=E4hle',225,224
)
insert into #tl values (240,29,'Messinstrumente ',226,241)
insert into #tl values (241,29,'Energieverbrauchsz=E4h',240,225
)
insert into #tl values (242,29,'Steuerungsmodule ',245,243)
insert into #tl values (243,29,'Storensteuerung ',242,232)
insert into #tl values (244,29,'Frequenzumrichter ',246,222)
insert into #tl values (245,29,'Kontrollrelais ',236,242)
insert into #tl values (246,29,'Antriebe ',223,244)
insert into #tl values (247,30,'IR-Steuertechnik ',248,0 )
insert into #tl values (248,30,'Geb=E4udesystemtechnik',0 ,247)
insert into #tl values (249,31,'AP-Steckdosen, trock',250,0 )
insert into #tl values (250,31,'AP-Fehlerstromschutz',251,249)
insert into #tl values (251,31,'AP-Steckdosen, feuch',252,250)
insert into #tl values (252,31,'AP-Steckdosen, nass ',253,251)
insert into #tl values (253,31,'AP-Fehlerstromschutz',0 ,252)
insert into #tl values (254,32,'UP-Steckdosen, trock',258,0 )
insert into #tl values (255,32,'UP-Fehlerstromschutz',0 ,257)
insert into #tl values (256,32,'UP-Steckdosen, feuch',257,258)
insert into #tl values (257,32,'UP-Steckdosen, nass ',255,256)
insert into #tl values (258,32,'UP-Fehlerstromschutz',256,254)
insert into #tl values (259,33,'EB-Steckdosen, trock',261,0 )
insert into #tl values (260,33,'EB-Steckdosen, feuch',0 ,261)
insert into #tl values (261,33,'EB-Fehlerstromschutz',260,259)
insert into #tl values (262,34,'Mehrfach-Stecker ',263,265)
insert into #tl values (263,34,'Fehlerstromschutzste',264,262)
insert into #tl values (264,34,'Apparatestecker ',0 ,263)
insert into #tl values (265,34,'Stecker ',262,0 )
insert into #tl values (266,35,'Industriesteckkontak',267,0 )
insert into #tl values (267,35,'Industrie-Steckkonta',268,266)
insert into #tl values (268,35,'Industrie-Steckkonta',0 ,267)
insert into #tl values (269,36,'Sicherungsmaterial (',270,0 )
insert into #tl values (270,36,'Leitungsschutzschalt',271,269)
insert into #tl values (271,36,'Sicherungselemente ',272,270)
insert into #tl values (272,36,'Kleinverteiler ',273,271)
insert into #tl values (273,36,'Aufbautableau/Z=E4hler',274,272)
insert into #tl values (274,36,'Hausanschlusskasten/',277,273)
insert into #tl values (275,36,'Apparategeh=E4use/Vert',276,277)
insert into #tl values (276,36,'Stromverteiler ',278,275)
insert into #tl values (277,36,'Z=E4hlerkasten/Zivilsc',275,274)
insert into #tl values (278,36,'NH-Sicherungsmateria',0 ,276)
insert into #tl values (279,37,'Kochplatten ',280,0 )
insert into #tl values (280,37,'Heizger=E4te ',282,279)
insert into #tl values (281,37,'Ventilatoren ',0 ,282)
insert into #tl values (282,37,'Heisswasserger=E4te ',281,280)
insert into #tl values (283,38,'Fluoreszenzarmaturen',284,0 )
insert into #tl values (284,38,'Vorschaltger=E4te/Star',285,283)
insert into #tl values (285,38,'Beleuchtungssysteme,',286,284)
insert into #tl values (286,38,'Gl=FChlampenarmaturen,',289,285
)
insert into #tl values (287,38,'Fassungen ',288,289)
insert into #tl values (288,38,'Lichtketten/Lichtsch',0 ,287)
insert into #tl values (289,38,'Notbeleuchtungen ',287,286)
insert into #tl values (290,39,'Gl=FChlampen/Halogengl',292,291)
insert into #tl values (291,39,'Fluoreszenzlampen ',290,0 )
insert into #tl values (292,39,'Entladungslampen ',293,290)
insert into #tl values (293,39,'LED-Lampen ',0 ,292)
insert into #tl values (294,40,'Stab-/Taschen-/Handl',295,0 )
insert into #tl values (295,40,'Elemente/Batterien/A',296,294)
insert into #tl values (296,40,'Schwachstromgl=FChlamp',297,295
)
insert into #tl values (297,40,'Transformatoren, Spe',298,296)
insert into #tl values (298,40,'L=E4utwerke/Summer/Hup',307,297)
insert into #tl values (299,40,'Besuchsanzeiger ',300,306)
insert into #tl values (300,40,'Sonnerietaster/Schal',301,299)
insert into #tl values (301,40,'Tasterplatten/Haust=FC',302,300)
insert into #tl values (302,40,'Steckdosen ',309,301)
insert into #tl values (303,40,'Buchsen/Klemmen/Kupp',304,309)
insert into #tl values (304,40,'T=FCr=F6ffner/T=FCr=F6ffnera',305,303)
insert into #tl values (305,40,'Solar-Material Syste',0 ,304)
insert into #tl values (306,40,'Drehspiegelleuchten,',299,308)
insert into #tl values (307,40,'Alarmsysteme ',308,298)
insert into #tl values (308,40,'=DCberwachungssysteme ',306,307)
insert into #tl values (309,40,'R=FCckwegtaugliche Kab',303,302)
insert into #tl values (310,41,'Verbindungsmuffen/Sp',311,0 )
insert into #tl values (311,41,'Montagezubeh=F6r ',312,310)
insert into #tl values (312,41,'Durchgangsdosen/Klem',313,311)
insert into #tl values (313,41,'Amtsverteilerkasten ',314,312)
insert into #tl values (314,41,'Hauptverteiler/Verte',315,313)
insert into #tl values (315,41,'Verbindungskasten ',316,314)
insert into #tl values (316,41,'Anschluss- und Durch',317,315)
insert into #tl values (317,41,'Steck- und Anschluss',322,316)
insert into #tl values (318,41,'Grobsicherungen, Tel',341,332)
insert into #tl values (319,41,'Stromsparschalter ',320,328)
insert into #tl values (320,41,'Teilnehmervermittlun',321,319)
insert into #tl values (321,41,'Telekommunikationsge',344,320)
insert into #tl values (322,41,'Anschlussmaterial RJ',330,317)
insert into #tl values (323,41,'Anschlussmaterial <L',337,338)
insert into #tl values (324,41,'Anschlussmaterial <T',325,337)
insert into #tl values (325,41,'Anschlussdosen <Thor',326,324)
insert into #tl values (326,41,'Anschlussmaterial <D',327,325)
insert into #tl values (327,41,'Verteilkasten ',340,326)
insert into #tl values (328,41,'USV-Anlagen ',319,340)
insert into #tl values (329,41,'Anschlussmaterial IS',343,342)
insert into #tl values (330,41,'Anschlussmaterial <I',332,322)
insert into #tl values (331,41,'Anschlussmaterial <P',346,351)
insert into #tl values (332,41,'Stecker <T+T 83>, <F',318,330)
insert into #tl values (333,41,'Anschlussmaterial Gl',334,352)
insert into #tl values (334,41,'Aktiv-Komponenten ',350,333)
insert into #tl values (335,41,'Anschlussmaterial <B',338,350)
insert into #tl values (336,41,'Anschlussysteme UGV ',348,347)
insert into #tl values (337,41,'Anschlussmaterial <F',324,323)
insert into #tl values (338,41,'Anschlussmaterial <A',323,335)
insert into #tl values (339,41,'Anschlussmaterial IS',351,343)
insert into #tl values (340,41,'Schutzger=E4te <Dehn>,',328,327)
insert into #tl values (341,41,'Anschlussmaterial IS',342,318)
insert into #tl values (342,41,'Anschlussmaterial IS',329,341)
insert into #tl values (343,41,'Anschlussmaterial IS',339,329)
insert into #tl values (344,41,'Messger=E4te ',0 ,321)
insert into #tl values (345,41,'Anschlussmaterial UG',353,349)
insert into #tl values (346,41,'Anschlussmaterial UG',347,331)
insert into #tl values (347,41,'Anschlussmaterial UG',336,346)
insert into #tl values (348,41,'Anschlussmaterial UG',349,336)
insert into #tl values (349,41,'Anschlussmaterial UG',345,348)
insert into #tl values (350,41,'Durchschaltdosen ',335,334)
insert into #tl values (351,41,'Anschlussmaterial AD',331,339)
insert into #tl values (352,41,'Anschlussmaterial <H',333,353)
insert into #tl values (353,41,'Anschlussmaterial UG',352,345)
insert into #tl values (354,42,'Werkzeuge f=FCr Telefo',361,362)
insert into #tl values (355,42,'Werkzeugtaschen, Wer',356,361)
insert into #tl values (356,42,'Planschutztaschen/Sc',357,355)
insert into #tl values (357,42,'Lager- und Transport',358,356)
insert into #tl values (358,42,'Schutzvorrichtungen ',359,357)
insert into #tl values (359,42,'Kabelbox, Kabelrolle',360,358)
insert into #tl values (360,42,'Messinstrumente ',0 ,359)
insert into #tl values (361,42,'Pr=FCfger=E4te ',355,354)
insert into #tl values (362,42,'Werkzeuge ',354,0 )
Regards,
Marc|||Fistly, design issues. Are you sure this is the real data and table
structure? All your columns are nullable, there is no primary key and
despite what you say there are no UNIQUE constraints either. If this is
accurate then you should redesign.
Also, "fk_Next_Knoten_ID" is a poor name for a column that is a foreign
key, but it's an even WORSE name for a column that is NOT a foreign
key, and yours appear not to be - no referential integrity is enforced
for the non-existent "0" row.
I'm guessing this is a more accurate representation:
CREATE TABLE tl
(
id INTEGER NOT NULL PRIMARY KEY,
tlid INTEGER NOT NULL,
titeld VARCHAR(50) NOT NULL,
next_knoten_id INTEGER NOT NULL,
/* NOT A FOREIGN KEY !!! */
previous_knoten_id INTEGER NOT NULL,
/* NOT A FOREIGN KEY!!! - also redundant */
UNIQUE (tlid, next_knoten_id),
UNIQUE (tlid, previous_knoten_id)
)
As there are no branches or loops, why not drop the redundant prev/next
pair of columns and use a sequence column instead? This is apparently
nothing more than an ordered list. What is the reason to create the
self-referencing columns?
As an ordered list, you would just need:
..
ORDER BY sequence_no
To order your present design as if it were a hierarchy you'll really
have to use a cursor. See Books Online "Expanding Hierarchies" for an
example.
David Portas
SQL Server MVP
--