Assign Same SNo to Parent and its Child

Question

I have a Table With name "TmpTable", this table have the Parent Child Relationship, table have data look like:

Id | Name      | ParentId
 1   PCs         0
 2   MACs        1
 3   Keyboard    0
 4   Mouse       0
 5   Screen      3
 6   Keyboard    4
 7   Mouse       0
 8   Screen      0
 9   Key         0          
 10  xyz         9          

Now I want to Select One Column With this Which shows the Sequence Number, in that Parent and Child have the Same S.No. and which did not have any child have Increased S.No., result will be same as below:

Id | Name      | ParentId | SNo
 1   PCs         0           1  
 2   MACs        1           1
 3   Keyboard    0           2
 4   Mouse       0           3
 5   Screen      3           2
 6   Keyboard    4           3
 7   Mouse       0           4
 8   Screen      0           5
 9   Key         0           6
 10  xyz         9           6

How can I archive this result please Guide/help me in this.

Answer 1

You can use DENSE_RANK() function in combination with ORDER BY CASE:

SELECT *, DENSE_RANK() OVER (ORDER BY CASE WHEN ParentID = 0 THEN ID ELSE ParentID END) 
FROM TmpTable
ORDER BY Id

SQLFiddle DEMO

Answer 2

You can try like this.

;with cte as 
(Select Row_Number() Over(Order by Id) as Row,Id from Table1 where Parentid=0
)
Select A.*,b.row as Sno from Table1 A inner join
cte as b on b.id=a.parentid or (b.id=a.id and a.parentid=0) order by a.id;

Sql Fiddle Demo