Solved

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

Posted on 2009-04-09
6
419 Views
Last Modified: 2012-06-27
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!
0
Comment
Question by:alexk23
6 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24114056
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
 
LVL 17

Expert Comment

by:HuyBD
ID: 24114059
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
 

Author Comment

by:alexk23
ID: 24119405
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 69

Accepted Solution

by:
Qlemo earned 500 total points
ID: 24121973
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24122076
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
 

Author Closing Comment

by:alexk23
ID: 31568561
i will try this thanks!
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question