Insert/join table on multiple conditions-CodePudding

I’ve a table that looks like this:

Table A

Version,id
5060586,22285

5074515,22701
5074515,22285

7242751,22701
7242751,22285

I want to generate a new key called groupId that is inserted as my example below:

Table A

Version,id,groupId
5060586,22285,1

5074515,22701,2
5074515,22285,2

7242751,22701,2
7242751,22285,2

I want the groupId to be the same as long as the id's are the same in the different versions. So for example version 5074515 and 7242751 has the same id's so therefor the groupId will be the same. If all the id's aren't the same a new groupId should be added as it has in version 5060586.

How can i solve this specific problem in SQL oracle?

CodePudding user response：

One approach is to create a unique value representing the set of ids in each version, then assign a groupid to the unique values of that, then join back to the original data.

INSERT ALL 
INTO t (version,id) VALUES (5060586,22285)
INTO t (version,id) VALUES (5074515,22701)
INTO t (version,id) VALUES (5074515,22285)
INTO t (version,id) VALUES (7242751,22701)
INTO t (version,id) VALUES (7242751,22285)
SELECT 1 FROM dual;

WITH groups
AS
(
SELECT version
, LISTAGG(id,',') WITHIN GROUP (ORDER BY id) AS group_text
FROM t
GROUP BY version
),
groupids
AS
(
SELECT group_text, ROW_NUMBER() OVER (ORDER BY group_text) AS groupid
FROM groups
GROUP BY group_text
)
SELECT t.*, groupids.groupid
FROM t
    INNER JOIN groups ON t.version = groups.version
    INNER JOIN groupids ON groups.group_text = groupids.group_text;

dbfiddle.uk

CodePudding user response：

You can use:

UPDATE tableA t
SET group_id = ( SELECT COUNT(DISTINCT id)
                 FROM   TableA x
                 WHERE  x.Version <= t.version );

Which, for the sample data:

CREATE TABLE TableA (
  Version  NUMBER,
  id       NUMBER,
  group_id NUMBER
);

INSERT INTO TableA (Version, id)
SELECT 5060586,22285 FROM DUAL UNION ALL
SELECT 5074515,22701 FROM DUAL UNION ALL
SELECT 5074515,22285 FROM DUAL UNION ALL
SELECT 7242751,22701 FROM DUAL UNION ALL
SELECT 7242751,22285 FROM DUAL;

Then, after the update:

SELECT * FROM tablea;

Outputs:

VERSION ID GROUP_ID

5060586 22285 1

5074515 22701 2

5074515 22285 2

7242751 22701 2

7242751 22285 2

VERSION	ID	GROUP_ID
5060586	22285	1
5074515	22701	2
5074515	22285	2
7242751	22701	2
7242751	22285	2

db<>fiddle here