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
Gives this:
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)
Gives:
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.
Best Answer
There are a few ways to do this. If there is a 1-2-1 relationship between the parents and widgets ( ie a parent only has one widget and vice versa ) you can just add a column in the CTE to count how many rows have passed through. See the xlevel example below.
If that doesn't work for you ( and I would probably expect a parent to have multiple widgets ) then you could just throw all the results into a temp table using the IDENTITY function, then grab the third row. Of course you need some kind of meaningful order for this to work, but see the #tmp table version below.
You could also use ROW_NUMBER(), available in SQL Server 2008 to add an arbitrary row number to the resultset. Again you need some kind of order to give it meaning. See the ROW_NUMBER example below:
HTH