Solved

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

Posted on 2009-04-09
6
421 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Copy data to New Year 9 33
IF SQL Query 12 29
Need help with convert Informix SQL SELECT statement to Microsoft SQL 1 26
SQL profiler 3 18
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

733 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