December 10, 2008 at 11:03 am
Hi all,
Using SQL2K.
Consider the following:
IF OBJECT_ID('tmp_list') IS NOT NULL
DROP TABLE tmp_list
GO
CREATE TABLE tmp_list
(nRow_IdINTEGER,
nLevelINTEGER,
cDescVARCHAR(16))
GO
INSERT tmp_list VALUES (1, 1, 'Heading1')
INSERT tmp_list VALUES (2, 2, 'SubHeading1')
INSERT tmp_list VALUES (3, 3, 'DetailA')
INSERT tmp_list VALUES (4, 2, 'SubHeading2')
INSERT tmp_list VALUES (5, 3, 'DetailB')
INSERT tmp_list VALUES (6, 2, 'SubHeading3')
INSERT tmp_list VALUES (7, 2, 'SubSubHeading1')
INSERT tmp_list VALUES (8, 3, 'DetailA')
INSERT tmp_list VALUES (9, 1, 'Heading2')
INSERT tmp_list VALUES (10, 2, 'SubHeading4')
INSERT tmp_list VALUES (11, 3, 'DetailC')
GO
I need to return rows in the following order:
1
2
6
7
3
4
5
9
10
11
suppressing the repeating 'DetailA' from row 8.
Not sure if it can be done in a single select.
Would very much prefer a non-cursor solution.
However, if RBAR is the only way, I will entertain a cursor/loop solution.
Thanks,
Paul
December 10, 2008 at 11:10 am
I think a clear flow of business logic would help here
how did you come up with the order below?
1 Heading1
2 SubHeading1
6 SubHeading3
7 SubSubHeading1
3 DetailA
4 SubHeading2
5 DetailB
9 Heading2
10 SubHeading4
11 DetailC
December 10, 2008 at 11:17 am
What you might want to do is build a table that includes this logic somehow, header before sub, etc. and then use that to join with this and order things by the 2nd table.
December 10, 2008 at 11:18 am
BTW: Rows 7 and 8 should be as follows (I forgot to increment the nLevels).
INSERT tmp_list VALUES (7, 3, 'SubSubHeading1')
INSERT tmp_list VALUES (8, 4, 'DetailA')
What I'm trying to do is to suppress repeated Details by moving all subheadings (and subsubheadings) leading to DetailA up to appear under the first subheading having DetailA.
I know that sounds off. Not sure how to put it otherwise...
December 10, 2008 at 1:11 pm
Done with nested cursors (curses!)
Does not significantly hinder perf -- in dev.
Now, let's try running 30 times a minute, with 250 users!
mwah-ah-ah-ah-ah-ah
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply