jo phul jo phul - 6 months ago 18
SQL Question

SQL Server : replace distinct values with ints when nested

Edited for MySQL.

I have a nested tree of data in a table and I need to number them. It's easy to find the distinct values, get the row number and then use that, by I have a nested tree and need to do that for each level.

My table looks like this (I'm showing three levels, but I actually have four or more)

col1, col2, col3, some data cols ....
-----------------------------------
'AAA', 'AA', 'A', #####
'AAA', 'AA', 'B', #####
'AAA', 'AA', 'C', #####
'AAA', 'BB', 'D', #####
'AAA', 'BB', 'E', #####
'AAA', 'BB', 'F', #####
'BBB', 'CC', 'G', #####
'BBB', 'CC', 'H', #####
'BBB', 'CC', 'I', #####
'BBB', 'DD', 'J', #####
'BBB', 'DD', 'K', #####
'BBB', 'DD', 'L', #####


etc...

There is more data than that, and AAA represents some string, etc

What I need is for each distinct in col1, replace (or put in a new table) the values 1, 2, 3, etc

Then for each distinct in col1, find the distinct in col2 and replace with 1, 2, 3, etc. Repeat for col3.

To get something like this

col1, col2, col3, some data cols ....
1, 1, 1, #####
1, 1, 2, #####
1, 1, 3, #####
1, 2, 1, #####
1, 2, 2, #####
1, 2, 3, #####
2, 1, 1, #####
2, 1, 2, #####
2, 1, 3, #####
2, 2, 1, #####
2, 2, 2, #####
2, 2, 3, #####


Is this doable via a SQL script?

So the answers provided work perfectly for Microsoft SQL.
Is there a way to do it in MySQL?
None of the functions seem to be present there.

Answer

try something like....

select 
  DENSE_RANK() OVER ( order by col1)                                 Col1
 ,DENSE_RANK() OVER (PARTITION BY col1 order by col1,col2)           Col2
 ,DENSE_RANK() OVER (PARTITION BY col1,col2 order by col1,col2,col3) Col3
From TableName