I have the following table.
| NumRegOper | nReg |
|---|---|
| 2067914121937973 | 1 |
| 2074206165945447 | 2 |
| 2074206165945447 | 3 |
| 2067914121937973 | 4 |
| 2074206165945098 | 5 |
If I use this script:
select ROW_NUMBER() OVER (order by NumRegOper desc) as NewOrder
, *
from #tmp
I got this result:
| NewOrder | NumRegOper | nReg |
|---|---|---|
| 1 | 2074206165945447 | 2 |
| 2 | 2074206165945447 | 3 |
| 3 | 2074206165945098 | 5 |
| 4 | 2067914121937973 | 1 |
| 5 | 2067914121937973 | 4 |
But what I want is that keeps the same 'NewOrder' when its the same NumRegOper identifier. Like this:
| NewOrder | NumRegOper | nReg |
|---|---|---|
| 1 | 2074206165945447 | 2 |
| 1 | 2074206165945447 | 3 |
| 2 | 2074206165945098 | 5 |
| 3 | 2067914121937973 | 1 |
| 3 | 2067914121937973 | 4 |
There is a way to do this in SQL Server?
CodePudding user response：
As John Cappelletti says in the comments the following will give you the desired result:
select DENSE_RANK() OVER(order by NumRegOper desc) as NewOrder ,* from #tmp
CodePudding user response：
You need to specify the "PARTITION BY" argument to get the result you want. ROW_NUMBER Documentation
select ROW_NUMBER() OVER(PARTITION BY NumRegOper order by NumRegOper desc) as NewOrder ,* from #tmp
Running these queries helps you understand better.
select ROW_NUMBER() OVER() Wrong_RN, * from #tmp order by NumRegOper desc
select ROW_NUMBER() OVER() Right_RN, * from #tmp GROUP BY NumRegOper order by NumRegOper desc