Selecting a random employee with adjacency tables

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();

One Response to “Selecting a random employee with adjacency tables”

  1. Mayiko says:

    Die order by newid() is echt wel vet.
    Werkt voor veel toestanden en gegarandeerde random.