>I have an interesting sorting problem:
>
>I have a table with three fields: ParentID, ChildID, and SortKey
>
>ParentID and ChildID are both PKs from another table. Any of the PKs from that table may appear any number of times as a ParentID, and any number of times as a ChildID.
>
>What I'm looking for is a way to assign SORTKEY so that I can process this table in the order I want, which is this --
>
>I want all occurrences of any PK as a ChildID to precede any occurrences of that same PK as a ParentID. Stated another way -- I want to have all the first generation PKs at the top, followed by all second generations, etc. Thus, when sorted, I will know that each ParentID I encounter will not be referenced later as a childID.
>
>There is some possibility with circularity here, of course, and I'm hoping that any solution might help that this issue as well.
>
>Thanks in advance.
Do you have NULL in ParentID for first generation (parents)? Also, do you want your result as a select statement or you somehow want it through the index?
If it's not broken, fix it until it is.
My Blog