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;