Getting a custom sized page from a sorted result set

Hi!
I've tried numerous solutions to this classic problem. I know how to do a
pretty scalable solution, but I'd like to hear some more ideas.
What I do now is something like this:
1. Declare a cursor inside an exec statement since order by doesn't support
variables
(I know about the alternative ORDER BY CASE @.param WHEN 1 THEN [column]
WHEN 2 THEN [column] DESC etc..., but it would need just as much or more
code)
2. Create a temporary table identical to the result set
3. Declare variables for all fields
4. Fetch from cursor [pagesize] times from [start] while inserting into
variables and then temporary table
5. Select from the temp table
6. Return total count of the result set for the pager mechanism
The main challenges I've had is how to fetch the correct page. In SQL Server
2005 I thought my troubles were gone since we got the new TOP(@.parameter)
feature, but when it comes to sorting it's still impossible.
Anyway, the demands are
1. Fast browsing of 5000+ rows big result sets
2. Sorting by any column
3. User defined page size
One alternative I'm considering is use a query returning the entire result
set, and then using a reader instead in .net, but the amount of data fetched
will still be too much when you reach the last page. I'm not sure though if
the sql injection removal code, the exec statement and the cursor will use
just as much resources... Should measure it someday, but if anyone allready
did it, I'd appreciate a link. :P
Anyone got a better solution than the one on top?
(Some MySQL fans I know mock me because they've got LIMIT, I want to hit
back.. ;) )
Lars-ErikIf I understand correctly, you want to retrieve N rows, starting from some
point in the result set.
You can solve part of the problem with set rowcount. You can use variables
so it can be passed to the stored procedure.
for example:
create procedure usp_GetPage
(
@.Number int
)
as
set rowcount @.number
select col1, col2
from table1
-- This will return @.Number rows from the table.
Paging is, offcourse more complicated but perhaps this helps a bit? The rest
is entirely up to the way you want to implement ordering and what would be
the criteria for defining pages (starting point and such)
MC
"Lars-Erik Aabech" <larserik@.newsgroup.nospam> wrote in message
news:eF4a%23UQ5FHA.2864@.tk2msftngp13.phx.gbl...
> Hi!
> I've tried numerous solutions to this classic problem. I know how to do a
> pretty scalable solution, but I'd like to hear some more ideas.
> What I do now is something like this:
> 1. Declare a cursor inside an exec statement since order by doesn't
> support variables
> (I know about the alternative ORDER BY CASE @.param WHEN 1 THEN [column]
> WHEN 2 THEN [column] DESC etc..., but it would need just as much or more
> code)
> 2. Create a temporary table identical to the result set
> 3. Declare variables for all fields
> 4. Fetch from cursor [pagesize] times from [start] while inserting into
> variables and then temporary table
> 5. Select from the temp table
> 6. Return total count of the result set for the pager mechanism
> The main challenges I've had is how to fetch the correct page. In SQL
> Server 2005 I thought my troubles were gone since we got the new
> TOP(@.parameter) feature, but when it comes to sorting it's still
> impossible.
> Anyway, the demands are
> 1. Fast browsing of 5000+ rows big result sets
> 2. Sorting by any column
> 3. User defined page size
> One alternative I'm considering is use a query returning the entire result
> set, and then using a reader instead in .net, but the amount of data
> fetched will still be too much when you reach the last page. I'm not sure
> though if the sql injection removal code, the exec statement and the
> cursor will use just as much resources... Should measure it someday, but
> if anyone allready did it, I'd appreciate a link. :P
> Anyone got a better solution than the one on top?
> (Some MySQL fans I know mock me because they've got LIMIT, I want to hit
> back.. ;) )
> Lars-Erik
>|||http://www.aspfaq.com/show.asp?id=2120
David Portas
SQL Server MVP
--|||Thanks guys :)
Both relevant info! Didn't know about the @.rowcount setting, and the
measures on the faq page was pretty interresting.
L-E
"Lars-Erik Aabech" <larserik@.newsgroup.nospam> wrote in message
news:eF4a%23UQ5FHA.2864@.tk2msftngp13.phx.gbl...
> Hi!
> I've tried numerous solutions to this classic problem. I know how to do a
> pretty scalable solution, but I'd like to hear some more ideas.
> What I do now is something like this:
> 1. Declare a cursor inside an exec statement since order by doesn't
> support variables
> (I know about the alternative ORDER BY CASE @.param WHEN 1 THEN [column]
> WHEN 2 THEN [column] DESC etc..., but it would need just as much or more
> code)
> 2. Create a temporary table identical to the result set
> 3. Declare variables for all fields
> 4. Fetch from cursor [pagesize] times from [start] while inserting into
> variables and then temporary table
> 5. Select from the temp table
> 6. Return total count of the result set for the pager mechanism
> The main challenges I've had is how to fetch the correct page. In SQL
> Server 2005 I thought my troubles were gone since we got the new
> TOP(@.parameter) feature, but when it comes to sorting it's still
> impossible.
> Anyway, the demands are
> 1. Fast browsing of 5000+ rows big result sets
> 2. Sorting by any column
> 3. User defined page size
> One alternative I'm considering is use a query returning the entire result
> set, and then using a reader instead in .net, but the amount of data
> fetched will still be too much when you reach the last page. I'm not sure
> though if the sql injection removal code, the exec statement and the
> cursor will use just as much resources... Should measure it someday, but
> if anyone allready did it, I'd appreciate a link. :P
> Anyone got a better solution than the one on top?
> (Some MySQL fans I know mock me because they've got LIMIT, I want to hit
> back.. ;) )
> Lars-Erik
>

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
--