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

I’m well aware that adjacency tables aren’t the newest way of storing hierarchies in a database. Personally I prefer to use nested sets. But on this one project I had to work with a legacy database. It took me quite a while to find out how to retrieve an entire tree in one requests. This is obviously needed to speed up queries on large amounts of data.

This query is here to help me remember how to do it.

DROP TABLE People;

CREATE TABLE People (
id INT NOT NULL PRIMARY KEY IDENTITY
, boss_id INT
, name VARCHAR(50)
);

SET IDENTITY_INSERT People ON

INSERT INTO People (id, boss_id, name)
          SELECT 1, NULL, 'Bert'
UNION ALL SELECT 2, 1, 'Sylvie'
UNION ALL SELECT 3, 1, 'Sylvia'
UNION ALL SELECT 4, 2, 'Davy'
UNION ALL SELECT 5, 2, 'Xavier'
UNION ALL SELECT 6, 3, 'Kristof'
UNION ALL SELECT 7, 3, 'Piet';

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 * 
FROM Bosses
ORDER BY Bosses.hierarchy;