How would you write this Query, maby CTE?
-
I have something like this:
Father Item
49588 71246
71246 72725
72725 80107
8 10525
10525 23456
I need to return the last child
Father Item
49588 80107
8 23456
¿Any idea?
-
How about some table definitions and sample data in the form of CREATE TABLE and INSERT statements so that we can better see what is going on? Also, what constitutes a child being the "last" child?
See the first link in my signature below.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
I've had to guess at what is a value 'Father' node, but gives the correct results with your data
declare @t table(Father int, Item int)insert into @t(Father,Item)
select 49588, 71246 union all
select 71246, 72725 union all
select 72725, 80107 union all
select 8, 10525 union all
select 10525, 23456;
with cte as (
select t.Father, t.Item
from @t t
where not exists(select * from @t t2 where t2.Item=t.Father)
union all
select c.Father,t.Item
from @t t
inner join cte c on c.Item=t.Father)
select c.Father,c.Item
from cte c
where not exists (select * from @t t where c.Item=t.Father);
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply