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!
alexk23Asked:
Who is Participating?
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
Yes, it does, and it relies on checking of the first level only. The following works with MSSQL 2005 (not 2000 because of except keyword).

select * from tbl where date_created >= '2/1/2009'
except
select child.* from tbl child join tbl parent
on child.date_created >= '2/1/2009' and parent.date_created >= '2/1/2009' and child.tree like parent.tree+'%' and parent.id != child.id

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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

0
 
HuyBDCommented:
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
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
alexk23Author Commented:
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?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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

0
 
alexk23Author Commented:
i will try this thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.