I have this SQL select:
x
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;