Get a specific row from a TSQL common table expression

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.

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:

;WITH GroupList(xlevel, WgID,WgParentWgID) AS (
SELECT 1 xlevel, WgID,WgParentWgID
FROM tblplWorkGroup WITH (NOLOCK) WHERE WgID = 283 AND WgActive=1

UNION ALL

SELECT xlevel + 1, 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
WHERE xlevel = 3
GO

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

;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 IDENTITY( INT, 1, 1 ) rowId, *
INTO #tmp
FROM GroupList
ORDER BY WgParentWgID, WgID
GO

SELECT *
FROM #tmp
WHERE rowId = 3
GO

;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
    (
    SELECT ROW_NUMBER() OVER( ORDER BY WgParentWgID, WgID ) rowId, *
    FROM GroupList
    ) x
WHERE rowId = 3
ORDER BY rowId
GO

HTH

StackExchange Database Administrators Q#82522, answer score: 2