I have a table like below in SQL Server. I reach the row number values with using ROW_NUMBER partition function. But this was not the result I wanted to achieve.
| RowNumber | Value |
|---|---|
| 1 | A |
| 2 | A |
| 3 | A |
| 1 | B |
| 2 | B |
| 1 | C |
| 1 | D |
I want to get this table
| RowNumber | Value |
|---|---|
| 1 | A |
| 1 | A |
| 1 | A |
| 2 | B |
| 2 | B |
| 3 | C |
| 4 | D |
How can I do it, do you have a function suggestion?
CodePudding user response：
Use DENSE_RANK:
SELECT DENSE_RANK() OVER (ORDER BY Value) RowNumber, Value
FROM yourTable
ORDER BY Value;
CodePudding user response：
Use DENSE_RANK instead of ROW_NUMBER
SELECT DENSE_RANK() OVER (ORDER BY VALUE), VALUE
FROM TABLE