I have this tsql code to recursively get the whole workgroup hierarchy for a specific workgroup (WgID 283 here):
WITH GroupList(WgID,WgParentWgID) AS ( SELECT WgID,WgParentWgID FROM tblplWorkGroup WITH (NOLOCK) WHERE WgID = 283 AND WgActive=1 UNION ALL SELECT B.WgID,B.WgParentWgID FROM GroupList A INNER JOIN tblplWorkGroup B WITH (NOLOCK) ON A.WgParentWgID = B.WgID WHERE B.WgActive=1 ) SELECT * FROM GroupList
WgID WgParentWgID 283 315 315 272 272 4 4 0
I need to get just the second to last row. I can do it by changing the last select statement.
SELECT * FROM GroupList WHERE WgParentWgID = (SELECT TOP (1) WgID FROM GroupList WHERE WgParentWgID = 0)
WgID WgParentWgID 272 4
However, if I want to get a nth row, it starts to get cumbersome. Is there a way to get a nth row from a WITH table expression? Or output row information in the output table too?
I tried converting the WITH expression to use a temporary table (where looks like I can get the nth row) but could not make the recursive functionality work. Any help is much appreciated.