Allocate a numerical order to data

[Kalisto]

I have some hierarchical data in a table. Linked via Parent Id's

eg.

pKey, Name, DisplayOrder, parentId

i.e.

1, 'Top Level',0,0
2, 'Tier Level',1,1
3, 'Sub Item 1',1,2
4, 'Sub Item 2',2,2
5, 'Tier Level',2,1
6, 'Sub Item 3',1,5
7, 'Sub Item 4',2,5

Some of the pages of my website can contain one or more of these items. (Managed by a cms)

The problem I have is that in each section, the ordering begins at 1. If I add an item to a page, and its position is susequently swapped (i.e. the display order for items 3 and 4 are swapped) then I can cope with that.

But, if I have items 3,4,6,7 on my page, and the order of their parents is swapped, I want to display 6,7,3,4.

so, how / what do I need to do so that I can query my hierarchy of data, and order them such that they will always be ordered in the right way, i.e. initially 1,2,3,4,5,6,7 but if the tier nodes are swapped, 1,5,6,7,2,3,4

All the select / order / group bys dont do what I want.

I have tried using the ROW_NUMBER() OVER function, but that doesnt quite seem to do what I need.

any ideas / comments / suggestions ?

 

[atilley]

If the number of tiers is fixed, I think you're going to need to join the table to itself for the number of tiers you have. Like this:

SELECT lev0.PK, lev0.Name, lev0.DisplayOrder, lev1.PK, lev1.Name, lev1.DisplayOrder, lev2.PK, lev2.Name, lev2.DisplayOrder
from
(
SELECT * FROM TIER WHERE ParentID = 0
) lev0
left join
TIER lev1
on
lev1.ParentID = lev0.PK
left join
TIER lev2
on
lev2.ParentID = lev1.PK

order by lev1.DisplayOrder, lev2.DisplayOrder


What about a recursive function which returns the "children" of an input ParentID?

 

[Kalisto]

The number of tiers is not definitely fixed, so I think the recursive method is the way to go.

However, as I understood it, functions in sql can only return a scalar value, and in reality I would need it to return a list (or table maybe) of all the items in their order.

I know I could do all this in my Database layer in my app, but Im trying to do it in Sql if possible, as it feels thats the right place to do it. (And then, if a section is moved or deleted, all the code happens in the db, rather than some in the db and some in the code.)

 

[bborissov]

Is this what you want?

DECLARE @Test TABLE (ID int, Name varchar(200), DispOrd int, Parent int)
INSERT INTO @Test VALUES (1, 'Top Level' , 0, 0)
INSERT INTO @Test VALUES (2, 'Tier Level', 1, 1)
INSERT INTO @Test VALUES (3, 'Sub Item 1', 1, 2)
INSERT INTO @Test VALUES (4, 'Sub Item 2', 2, 2)
INSERT INTO @Test VALUES (5, 'Tier Level', 2, 1)
INSERT INTO @Test VALUES (6, 'Sub Item 3', 1, 5)
INSERT INTO @Test VALUES (7, 'Sub Item 4', 2, 5)



;WITH TestCTE (ID, Name, DispOrd, Parent) AS 
(
  SELECT ID, Name, DispOrd, Parent
         FROM @Test Tst
  WHERE Parent = 0
  UNION ALL
  SELECT Tst1.ID, Tst1.Name, Tst1.DispOrd, Tst1.Parent
         FROM @Test Tst1
  INNER JOIN TestCTE ON TestCtE.Id = Tst1.Parent
)

SELECT * FROM TestCTE
ORDER BY Parent, DispOrd

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Kalisto]

Its a little late here, I'll run that by in the morning and see how it looks!

Thanks

 

[hmckillop]

Just a quick note - you say in your second post that functions only return scalars, this is not exactly the case, functions can return tables.

have a look at the second example (example B) on the microsoft definition of CREATE FUNCTION

http://msdn.microsoft.com/en-us/library/ms186755.aspx

Alternatively have a look at example c looks to be exactly what you need - however Bborissov has already posted the basis for this.

Good luck,

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Kalisto]

Thanks BBorissov, I can see what it does, but it doesnt actually solve what Im trying to do and my SQL isnt good enough to unravel / fix what you posted, so I think Im going to give up and resolve it via my code.

Thanks to both of you for trying to help..

 

[bborissov]

OK, from that DATA you posted what is desired result?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Kalisto]

the sql you gace me worked fine, and produced for me the basis of the hierarchy
i.e

records in the order of
1,2,5,3,4,6,7

but if my page was displaying records 3,4,5

I would want to see these items in the order 3,4,5 as 3 and 4 are to the left of 5 in the overall hierarchy of data.

Using the sample you gave me, I would see 3,5,4
I guess in some way I need to be able to order / rank them based on some sort of compound key that is Items Parent Display Id, then the Item display Id

so in that way, item 2 would be 10 (display order 1, no childred), item 3 would be 11, item 4 12, item 5 20 and so on.

That way, I could order on this compound key and it would always display them on this page in the correct order.

However these samples I am using are representative of my data, but not the actual data wher ethe nesting levels can be several more layers deep.

 

[bborissov]

DECLARE @Test TABLE (ID int, Name varchar(200), DispOrd int, Parent int)
INSERT INTO @Test VALUES (1, 'Top Level' , 0, 0)
INSERT INTO @Test VALUES (2, 'Tier Level', 1, 1)
INSERT INTO @Test VALUES (3, 'Sub Item 1', 1, 2)
INSERT INTO @Test VALUES (4, 'Sub Item 2', 2, 2)
INSERT INTO @Test VALUES (5, 'Tier Level', 2, 1)
INSERT INTO @Test VALUES (6, 'Sub Item 3', 1, 5)
INSERT INTO @Test VALUES (7, 'Sub Item 4', 2, 5)



;WITH TestCTE (ID, Name, DispOrd, Parent, HLevel) AS
(
  SELECT ID, Name, DispOrd, Parent, 0 AS HLevel
         FROM @Test Tst
  WHERE Parent = 0
  UNION ALL
  SELECT Tst1.ID, Tst1.Name, Tst1.DispOrd, Tst1.Parent, TestCTE.HLevel+1 AS HLevel
         FROM @Test Tst1
  INNER JOIN TestCTE ON TestCtE.Id = Tst1.Parent
)

SELECT * FROM TestCTE
ORDER BY Parent, DispOrd

?
That way you will have the hierarchy level of each record.
Did this help?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Kalisto]

I'll try it tomorrow.
For what its worth Ive done it with my code and a recursive method, but Im always keen to do things properly and learn, so I'll try it an dretrofit if it does

thanks, I'll post tomorrow how it went