Help with Recursive Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Recursive Query

Author

Topic

JWong
Starting Member

5 Posts

Posted - 2009-09-19 : 00:39:53

Hi Friends:

I have a genealogy table that looks like the following. What I'd like to run a query that:

1. Add a column 'SubGrp' at the end, and
2. Assign a SubGrp value for people within the same generation but with a different set of parents. So, for example, a SubGrp value of 1 would be assign to Indi_ID 5 and 6, a SubGrp value of 2 would be assigned to Indi_ID 7 and 8, etc.

I don't want to use a cursor and iterate through records one at a time. I'm thinking that there has to be a way to achieve what I want via CTE, but I just can't wrap my mind around how to do so.

Any help would be greatly appreciated!

Sincerely,

Justin

Indi_ID Dad_ID Mom_ID Generation
5 1 2 1
6 1 2 1
7 1 4 1
8 1 4 1
9 1 3 1
10 1 2 1
11 1 2 1
12 1 2 1
45 44 12 2
46 44 12 2
47 44 12 2
38 37 11 2
39 37 11 2
40 37 11 2
41 37 11 2
42 37 11 2
43 37 11 2
35 9 34 2
36 9 34 2
31 49 8 2
32 49 8 2
33 49 8 2
27 48 7 2
28 48 7 2
29 48 7 2
30 48 7 2
21 6 14 2
22 6 14 2
23 6 15 2
24 6 15 2
25 6 15 2
26 6 15 2
16 5 13 2
17 5 13 2
18 5 13 2
19 5 13 2
20 5 13 2

Andreas
Starting Member

11 Posts

Posted - 2009-09-19 : 11:49:51

You don't have to use a recursive query with CTE, a subquery with a ranking window function will do just fine:

The table I used looks like this:

create table #Genealogy
(
	Indi_ID int,
	Dad_ID int,
	Mom_ID int,
	Generation int
)

And the query:

select
	G.Indi_ID,
	G.Dad_ID,
	G.Mom_ID,
	G.Generation,
	t.SubGrp
from
	#Genealogy G
	inner join
	(
		select
			Dad_ID,
			Mom_ID,
			Generation,
			dense_rank() over(order by Dad_ID, Mom_ID, Generation) SubGrp
		from #Genealogy
	) t
		on t.Dad_ID = G.Dad_ID
		and t.Mom_ID = G.Mom_ID
		and t.Generation = G.Generation
group by
	G.Indi_ID,
	G.Dad_ID,
	G.Mom_ID,
	G.Generation,
	t.SubGrp
order by t.SubGrp

JWong
Starting Member

5 Posts

Posted - 2009-09-19 : 13:20:34

Thanks so much Andreas. I learn something new!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources