Home > Mobile >  sql query to group all the rows with same column value
sql query to group all the rows with same column value

Time:01-07

userid tenantid
null a001
null a002
null a002
null a002
null a001
null a003
null a002
null a003
null a001
null a002

I want to set the userid as "distinct_user_#" for the rows with same tenant ids. I can't set the userid manually as tenantids are generated randomly

So output would be something like

userid tenantid
d_u_1 a001
d_u_2 a002
d_u_2 a002
d_u_3 a003
d_u_1 a001
d_u_3 a003
d_u_2 a002
d_u_3 a003
d_u_1 a001
d_u_2 a002

Any help with this?

CodePudding user response:

We can use DENSE_RANK() here:

SELECT 'd_u_'   CAST(DENSE_RANK() OVER (ORDER BY tenantid) AS varchar(12)) AS userid,
       tenantid
FROM yourTable;

CodePudding user response:

You can use the dense_rank function to generate this id:

SELECT 'd_u_'   DENSE_RANK() OVER (PARTITION BY tenantid ORDER BY tenantid) AS userid,
       tenantid
FROM   mytable
  • Related