Skip to content
Advertisement

How to replace identical values with incremental integers?

I have this SQL select:

SELECT UserId, Level 
FROM Users 
ORDER BY UserId

The output will look something like this:

UserId      Level       
-------------------
Alex        Easy        
Alex        Medium        
Alex        Hard        
Jessica     Hard       
Tom         Medium        
Tom         Easy        

How can I replace the identical values of UserId with incremental integers, so that the output will look something like this:

UserId      Level       
---------------------
1           Easy        
1           Medium        
1           Hard        
2           Hard       
3           Medium        
3           Easy        

I have tried grouping the identical UserId rows first then joining it with the same table while having an incremental int declared but no success there. It seems like there must be an easier way to do this?

Advertisement

Answer

You could use DENSE_RANK:

SELECT DENSE_RANK() OVER (ORDER BY UserId) AS UserId, Level
FROM yourTable
ORDER BY 1;

screen capture from demo link below

Demo

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement