From: RS on
This is rather a unique question which came up during a discussion
with one of our developers, where they want only SQL views and no
stored procedure at all. Here is the problem description:

I've a table that holds the Employee ID and Supervisor ID columns in

For every employee, there is a supervisor except for one employee.
Every supervisor is an employee and is stored in the same table.
There is one to one relationship between employee and supervisor.

I am tasked to write a query that provides an output for every
employee with their rank in the company.
In other words, the employee with no supervisor is ranked as number 1
employee (for instance CEO),
every one under this one employee is ranked as number 2 employees (for
instance Vice Presidents),
every employee under these supervisors are ranked as number 3
employees (for instance Managers),
so on and so forth.

I need a query that gives me rank of the employee and their employee
Is there a way to write this in a SQL view? I've got SQL2008.
From: --CELKO-- on
Get a copy of TREES & HIERARCHIES IN SQL and read about the nested
sets model. This is very easy once you stop thinking in the
procedural adjacency list mindset.
From: Plamen Ratchev on
You can use a recursive CTE in a view. Here is example:

CREATE TABLE Employees (
supervisor INT NULL REFERENCES Employees (employee),
CHECK (employee <> supervisor));

(1, NULL),
(2, 1),
(3, 1),
(4, 2),
(5, 2),
(6, 4);


CREATE VIEW EmployeeHierarchy
WITH Hierarchy AS (
SELECT employee, supervisor, 1 AS employee_rank
FROM Employees
WHERE supervisor IS NULL
SELECT E.employee, E.supervisor, H.employee_rank + 1
FROM Employees AS E
JOIN Hierarchy AS H
ON E.supervisor = H.employee)
SELECT employee, supervisor, employee_rank
FROM Hierarchy;


SELECT employee, supervisor, employee_rank
FROM EmployeeHierarchy
ORDER BY employee_rank, employee;


employee supervisor employee_rank
----------- ----------- -------------
1 NULL 1
2 1 2
3 1 2
4 2 3
5 2 3
6 4 4


Plamen Ratchev
From: RS on
Thanks Plamen Ratchev - that worked great - appreciate your help.
From: Pedro DeRose [MSFT] on
Another option that may be worth considering is using the hierarchyid type
in SQL Server 2008. It makes this rank query trivial, and has several other
performance benefits (constant-time insertion, fast descendant queries, and

The first part of this presentation gives an introduction:

With some demo code here:

This BOL tutorial is also helpful:


Pedro DeRose
Program Manager, Microsoft SQL Server

(Legalese: This posting is provided "AS IS" with no warranties, and confers
no rights. Use of included script samples are subject to the terms specified