Prev: Any way to select all fields except 1?
Next: CLR integration gotchas!
From: RS on 10 Feb 2010 18:26
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
it.

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
id.
Is there a way to write this in a SQL view? I've got SQL2008.
From: --CELKO-- on 10 Feb 2010 19:57
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 10 Feb 2010 22:02
You can use a recursive CTE in a view. Here is example:

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

INSERT INTO Employees VALUES
(1, NULL),
(2, 1),
(3, 1),
(4, 2),
(5, 2),
(6, 4);

GO

CREATE VIEW EmployeeHierarchy
AS
WITH Hierarchy AS (
SELECT employee, supervisor, 1 AS employee_rank
FROM Employees
WHERE supervisor IS NULL
UNION ALL
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;

GO

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
http://www.SQLStudio.com
From: RS on 11 Feb 2010 00:27
Thanks Plamen Ratchev - that worked great - appreciate your help.
From: Pedro DeRose [MSFT] on 11 Feb 2010 15:25
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
others).

The first part of this presentation gives an introduction: http://skydrive.live.com/self.aspx/.Public/Semi-structured/Managing%20Semi-Structured%20Data.pptx

With some demo code here:
http://skydrive.live.com/self.aspx/.Public/Semi-structured/semistructured.sql

This BOL tutorial is also helpful:
http://technet.microsoft.com/en-us/library/bb677173.aspx


Cheers,

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
at http://www.microsoft.com/info/cpyright.htm.)


 |  Next  |  Last
Pages: 1 2
Prev: Any way to select all fields except 1?
Next: CLR integration gotchas!