EXPLAIN EXTENDED

How to create fast database queries

Archive for March 3rd, 2009

First common ancestor

with 2 comments

From Stack Overflow:

Let's say that we have we have a table with the classic manager_id recursive relationship:

Users (user_id int, manager_id int) (refers to user_id)

If you randomly select 2 rows in the table, or 2 nodes, how do you find the lowest level common ancestor? My platform is SQL Server 2005 (Transact-SQL), but any ANSI compliant SQL will also work...

Very nice question.

This may be useful to check against any kind of common ancestry in a tree structure: classes, folders, etc.
Read the rest of this entry »

Written by Quassnoi

March 3rd, 2009 at 11:00 pm

Posted in SQL Server