Kristof Neirynck

In my previous blogpost I mentioned a way to retrieve the entire tree from a hierarchy stored with adjacency tables. Now I’ve got a way to retrieve a random employee in an organisation.

I use ORDER BY NEWID() to randomize the order and TOP 1 to get only one result.

WITH Bosses (id, boss_id, name, depth, hierarchy)
AS (
	SELECT id
	, boss_id
	, name
	, 0
	, name
	FROM People
	WHERE boss_id IS NULL
	
	UNION ALL
	
	SELECT People.id
	, People.boss_id
	, People.name
	, Bosses.depth + 1
	, CONVERT(VARCHAR(50), Bosses.hierarchy + ' > ' + People.name)
	FROM People
	, Bosses
	WHERE People.boss_id = Bosses.id
)
SELECT TOP 1 * 
FROM Bosses
WHERE depth >= 2
ORDER BY NEWID();

This entry was posted on 2009-02-24 at 10:30 and is filed under sql. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.