organize sql heirarchy

Question

I have the following table:

Id   Son  RowOrder   Technology
1     8     NULL       fa
8     0     NULL       fa
9     15    NULL       gr
15    0     NULL       gr

I would like to create an sql query that will do an "order by" by the following order: technology, "father" (a record that has a son), son (the direct son of the previous father" and do an update to the RowOrder column so next time i will order these records sole based on the RowOrder. any ideas? thanks

Answer 1

If you do not want data to change then instead of storing the data in row number just use

SELECT 
   id,
   son, 
   technology,
   ISNULL((select id from table t2 where t2.id = t1.son), 0) AS father,
   ROW_NUMBER() OVER (ORDER BY technology, father, son) AS RowNumber
FROM 
   table t1 
ORDER BY 
   RowNumber ASC

the functions ISNULL() and ROW_NUMBER() may change name depending on what database you are using but that is a rough idea of what you should go for.