Thanks guys - that's solved a problem. Appreciated 😉

Right, thanks Thom, that explains a lot. I wonder if I can pick your brains a little more? I'm in a situation where I'm having to "manage" the guys configuring...

Happy Christmas guys.

Just for info there is no hierarchy, each item is at the same level, it's a relationship between items which have different PKs but are the same design,...

Well my solution ignores the problem of 1:1 entries, as does Peter's, which runs in 4 seconds and looks to be spot on (aside from the face that it results...

Well I can't be precise because I reloaded the source data this morning and I haven't run it today, but yesterday it was 15,760 with maximum group sizes of between...

Hi Jeff,

Here's a sample dataset, my bad, 89,000 is a different number, it's just 50,000 pairs of internal keys.

@chris-2 - Thanks for the offer, appreciated, I'll have...

Hey, Jeff Moden, you've taught me a lot over the years (you might not believe it looking at my code :-))...

OK, so we're only looking at about 89,000 records, so...

Thanks for that Chris....

Right, so now I understand how a recursive CTE works. Doh! There's a certain point when I'm reading into things that my brain just gives up and...

That's quite useful Thom, could I ask what you're looking at to do that check? I was assuming that lifting the parameters would occur prior to the report being rendered,...

Yes, that was kinda my thinking, thanks for the reassurance. I suppose it might be worth putting the update inside a transaction in case the database flakes out mid flow.