"Cluster" Code Help in SQL

Question

I am relative newcomer to SQL, but have gained many useful ideas through the site. Now I'm stuck on a piece of code that seems simple enough, but for some reason I can't wrap my head around it.

I am trying to create a third column (Column Z) based off of the first two columns below:

Column X   Column Y
-------------------
 1          a
 1          b
 1          c
 2          a
 2          d
 2          e
 2          f
 4          b
 5          i
 5          c
 3          g
 3          h
 6          j
 6          k
 6          l

What i need to have happen in Column Z:

• For each individual value found in Column Y, note the value of Column X
• Likewise, for each individual value in Column X, note the value of Column Y
• Then, cluster (RANK/ROW_NUMBER?) these into groups seen below:

Column X   Column Y  Column Z
-----------------------------
 1          a         1
 1          b         1
 1          c         1
 2          a         1
 2          d         1
 2          e         1
 2          f         1
 4          b         1
 5          i         1
 5          c         1
 3          g         2
 3          h         2
 6          j         3
 6          k         3
 6          l         3

I hope I've been clear enough without over-complicating things. My head has been spinning all morning. Let me know if anyone needs any more info.

Greatly appreciated in advance!

Answer 1

I have faced exactly this problem for some analyses in the past. The only way I could get it to work is by doing a loop, that incrementally adds in the information.

The loop assigns the minimum "x" value within each group as the group id. By your rules, this is guaranteed to be unique. It starts by assigning the current x value to z. It then finds the minimum z along the x and y dimensions. It repeats this process until no records change.

Given your data, the following is an outline of how to do it:

update t set z = x

while 1=1
begin
    with toupdate as (
         select t.*,
                min(z) over (partition by x) as idx,
                min(z) over (partition by y) as idy from t
         )
    update toupdate
        set z = (case when idx < idy then idx else idy end)
        where z > idx or z > idy;
    if (@@ROWCOUNT = 0) break;   
end;

;with a as
(
  select z, dense_rank() over (order by z) newZ from t
)
update a set z = newZ

Answer 2

Maybe not the best way, but it works

SQLFiddle http://sqlfiddle.com/#!3/99532/1

;WITH cte AS (
    SELECT  *, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS row_nb
    FROM    #t
)
, c2 AS (
    SELECT e1.*
    ,CASE WHEN EXISTS(SELECT * FROM cte e2 WHERE e1.Y = e2.Y and e2.row_nb < e1.row_nb) THEN 1 ELSE 0 END as ex
FROM cte e1 
)
, c3 AS (
    SELECT  X,1 - SIGN(SUM(ex)) as ex,MAX(row_nb) as max_row_nb
    FROM    c2
    GROUP BY X
)
SELECT  
    cte.X,cte.Y
    ,(SELECT SUM(cc3.ex) FROM c3 cc3 where cc3.max_row_nb<= c3.max_row_nb) AS Z 
FROM    cte 
INNER JOIN c3 
    ON  c3.X = cte.X
ORDER BY cte.row_nb