asked on

select most senior node out of a result set representing a hierarchy of nodes

I have a table the stores data of hierarchical entities.

eg

id | tree | parent_id | date_created
======================================
1 1. null 2/1/2009
2 1.2. 1 2/1/2009
3 3. null 1/1/2009
4 3.4 3 2/1/2009
5 3.4.5. 4 2/2/2009

If i query like this
select * from table where date_created >= 2/1/2009, my result set looks like this

id | tree | parent_id | date_created
======================================
1 1. null 2/1/2009
2 1.2. 1 2/1/2009
4 3.4 3 2/1/2009
5 3.4.5. 4 2/2/2009

However, I'm not interested in all of the children. I only want to select the top most parent with a single select without using a CTE
so I want:

id | tree | parent_id | date_created
======================================
1 1. null 2/1/2009
4 3.4 3 2/1/2009

Note that in the result set above, the top most parent returned might still have a parent in the table (meaning it doesn't necessarily need to be at the root of the tree), but b/c I'm only interested at the most senior parent after a certain date, it should be returned in my final result set, even though technically its a child of some other node in the table.

The tree column is a indexed varchar.

Does anyone have a concise way of doing this?

Thanks!

Raja Jegan R

Hope this is what you require:

Replace the '?' with date values accordingly

Have tested and is working fine

SELECT i.id, i.tree, i.parent_id, i.date_created
FROM id i INNER JOIN (
SELECT min(id) ID
FROM id 
WHERE parent_id IS NOT NULL
GROUP BY substring(tree, 1, 2) ) t1 ON t1.id = i.id
LEFT OUTER JOIN (
SELECT min(id) id
FROM id 
WHERE parent_id IS NOT NULL
AND date_created = ?
GROUP BY substring(tree, 1, 2)) t2 ON t2.id = i.id

Open in new window

HuyBD

try this:

select * from table where date_created >= 2/1/2009 and (parent_id is null or parent_id in(select id from table where parent_id is null))

Hope this help

alexk23

ASKER

rrjegan17, does your solution depend on knowing the length of each ID in the bread crumb. IE
substring(tree,1,2) would not work on a tree that looks like 1234.4567.89001 right?

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Raja Jegan R

Slight Modification to accomodate that in my query. Removed Hardcoding of 1, 2
Tested it and revert me

SELECT i.id, i.tree, i.parent_id, i.date_created
FROM id i INNER JOIN (
SELECT min(id) ID
FROM id 
WHERE parent_id IS NOT NULL
GROUP BY substring(tree, 1, charindex('.',tree)) ) t1 ON t1.id = i.id
LEFT OUTER JOIN (
SELECT min(id) id
FROM id 
WHERE parent_id IS NOT NULL
AND date_created = getdate() - 30
GROUP BY substring(tree, 1, charindex('.',tree))) t2 ON t2.id = i.id

Open in new window

alexk23

ASKER

i will try this thanks!