Good Day all
it is Probably a long day, i cant think Straight now.
i have a table that looks like this
Nodeid Parent Description Type Curr ========================================================================================== 89 NULL Compulsory 1 10 90 89 B1052 3 10 2820 89 One of 2 10 4113 89 B1061 3 10 2821 2820 B1054 3 10 2822 2820 B1055 3 10
Now the Red Record needs to be on top of "One of" because the Parent is "Compulsary" with the "Parent" = 89. Now Even "One of " has the Same Parent as the red record but if its a "One of " and they have the same parent, then "One of " must always be below the record. What i mean is that if there is a record with a same parent but different Type , the one that has type 2 should go below the one that has type 3 in my query. here is my query
select distinct nP.id, nP.NodeID, nP.parent, nP.Description, nRef.ID refParent, np.type from #Nodes nP left outer join #Nodes nRef on nP.Parent = nRef.NodeID -- look up the reference id of the parent order by refParent,nP.id,np.type desc