Grouping mapping table records

Question

Grouping mapping table records

alex.walmsley

Newbie

Points: 5
More actions
June 23, 2016 at 7:40 am

#400311

Hi,
First question. Apologies for newbie errors.
I have a simple mapping table with related records like the following, just using IDs.
(IdA, IdB)
(1,1),
(1,2),
(2,2),
(2,3),
(3,4),
(4,4),
(4,5)
You can see the first 4 records are interlinked. I.e IdA = 1 appears twice, IdB = 2 appears twice and is linked to another IdA (2), which in turn is linked to another IdB. That's the first group because there is then no other relation between any of the other Ids.
So I need to write the query to give an extra column that will group those that are interrelated and give them a group Id, like the following result
(IdA, IdB, GroupId)
(1,1,1),
(1,2,1),
(2,2,1),
(2,3,1),
(3,4,2),
(4,4,2),
(4,5,2)
...
It feels so simple but I can't get it working after trying with self-joins and recursive CTEs. I'm writing a view so I'll be avoiding loops where possible.
Any help out there? Thanks.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 741018 More actions · Answer 1

A couple things. First idea is that there isn't any order here. So there isn't a "first" concept. I'm hoping you realize that. With that in mind, other than order, how can you tell things are inter-related? How is 2, 3 related, but not 3, 4?

Usually when we want to group together items, we'll use some sort of OVER() function, perhaps with a ROW_NUMBER(). This feels like that kind of issue, but I'm not sure how to find the groups. You need logical ways to examine these.

If you're looking at numerical order, that helps. If you want to start with those groups that have a duplicate (1,1), then you could do a CTE that might find you those groups in order. Perhaps you can then query that with some ordering method?

Jeff Moden SSC Guru Points: 1004701 More actions · Answer 2

alex.walmsley (6/23/2016)
Hi,
First question. Apologies for newbie errors.
I have a simple mapping table with related records like the following, just using IDs.
(IdA, IdB)
(1,1),
(1,2),
(2,2),
(2,3),
(3,4),
(4,4),
(4,5)
You can see the first 4 records are interlinked. I.e IdA = 1 appears twice, IdB = 2 appears twice and is linked to another IdA (2), which in turn is linked to another IdB. That's the first group because there is then no other relation between any of the other Ids.
So I need to write the query to give an extra column that will group those that are interrelated and give them a group Id, like the following result
(IdA, IdB, GroupId)
(1,1,1),
(1,2,1),
(2,2,1),
(2,3,1),
(3,4,2),
(4,4,2),
(4,5,2)
...
It feels so simple but I can't get it working after trying with self-joins and recursive CTEs. I'm writing a view so I'll be avoiding loops where possible.
Any help out there? Thanks.

My first question would be... are the values IdA and IdB representative of rows located in different tables or the same table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)