3

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.

  • What about converting it to a function that returns a table? Then you could use a parm and pass it in. – Chris Aldrich Nov 12 '14 at 21:33
  • @ChrisAldrich I have it in a function and I'll be passing the row as a param. I just couldn't figure out how to get the nth row from the table here. – halfwise7 Nov 12 '14 at 22:04
2

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

  • Thanks so much for the detailed response! I ended up using the second option but since I was using it in a function I changed the temp table into a table variable. Also needed to count the no of rows in the table and determine the requested row based on that so that worked out well. – halfwise7 Nov 14 '14 at 15:18
  • Glad that worked! – wBob Nov 14 '14 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.