Sql Recursive Query Only Return The Last Row

I am trying to get the simple SQL Server 2008 Recursive Query to work. Following these examples: http://msdn.microsoft.com/en-us/library/ms186243.aspx and SQL Server recursive quer

Solution 1:

with recury as (
Select 
fs1.ID ,fs1.FParent,fs1.FName 
from  FoldersStructure as  fs1
where fs1.ID=8

union all
select fs2.id,fs2.FParent,fs2.FName 
from FoldersStructure as  fs2
inner join recury as r on fs2.ID= r.FParent

)
select ID,FParent,FName
from recury 
orderby ID 

SQL-Fiddle

Solution 2:

Remove the WHERE clause from the statement because it is limiting the resultset to rows where Id = 8. Based on the first comment below, I now understand your requirement! To use 8 as your starting point and to retrieve all parent rows:

WITH recury (Id, ParentId, Name, Level) AS
(
  SELECT fs1.Id ,fs1.ParentId,fs1.Name, CONVERT(int, 0)
  FROM  FoldersStructure AS  fs1
  WHERE fs1.Id = 8
  UNION ALL
  SELECT fs2.Id,fs2.ParentId,fs2.Name, Level - 1FROM FoldersStructure AS  fs2
  JOIN recury AS r ON fs2.Id = r.ParentId
)
SELECT Id, ParentId, Name, Level
FROM recury 
ORDERBY  Level;

This code will work if the Ids of the parent rows are not in numeric order. If your parent rows always guaranteed to be in numeric order, you can omit the Level column introduced in the CTE and sort on the Id column instead as per bummi's answer.

SQL fiddle example: http://sqlfiddle.com/#!3/2af0c/4