Assign numbers based on combination in sql

NAMITHA ANTONY 2048044 0
Viewed 36

How to assign ids based on combination in sql

Data

enter image description here

what i tried

SELECT *, dense_rank() over(order by colA)  FROM Table

Ids should be assigned based on combination of colA and colB. The value in colA and colB should have same id

Expected output: enter image description here

sql
1 Answers

You can assign unique id values based on whether the colA or colB value has been seen before by adding row numbers to the table, then left-joining the new table to itself based on the row number being less and a match on one of the colA or colB values. Where there is a match, select the minimum row number from the joined table as the id, otherwise select the current row number as the id. This gives a set of unique, but non-contiguous id values, which can be made contiguous by using DENSE_RANK:

WITH RNS AS (
  SELECT *, ROW_NUMBER() OVER () AS rn
  FROM test
),
IDS AS (
  SELECT t1.coLA, t1.colB, t1.rn, MIN(COALESCE(t2.rn, t1.rn)) AS id
  FROM RNS t1
  LEFT JOIN RNS t2 ON t1.rn > t2.rn
    AND (t1.colA = t2.colA OR t1.colA = t2.colB OR
         t1.colB = t2.colA OR t1.colB = t2.colB)
  GROUP BY t1.coLA, t1.colB, t1.rn
  ORDER BY t1.rn
)
SELECT colA, colB, DENSE_RANK() OVER (ORDER BY id) AS id
FROM IDS
ORDER BY rn

Output (for your sample data):

colA    colB    id
a       b       1
b       c       1
a       d       1
e       f       2
g       h       3
i       h       3
b       k       1

Demo on db-fiddle

usergAKD0
Related