Sql Server Loop For Changing Multiple Values Of Different Users

I have the next table: Supposing these users are sort in descending order based on their inserted date. Now, what I want to do, is to change their sorting numbers in that way tha

Solution 1:

You can use the ROW_NUMBER ranking function to calculate a row's rank given a partition and order.

In this case, you want to calculate row numbers for each user PARTITION BY User_ID. The desired output shows that ordering by ID is enough ORDER BY ID.

SELECT 
    Id, 
    User_ID, 
    ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY Id) AS Sort_Number
FROM MyTable

There are other ranking functions you can use, eg RANK, DENSE_RANK to calculate a rank according to a score, or NTILE to calculate percentiles for each row.

You can also use the OVER clause with aggragets to create running totals or moving averages, eg SUM(Id) OVER (PARTITION BY User_ID ORDER BY Id) will create a running total of the Id values for each user.

Solution 2:

use ROW_NUMBER() PARTITION BY User_Id

SELECT
    Id,
    [User_Id],
    Sort_Number = ROW_NUMBER() OVER(PARTITION BY [User_Id] 
                              ORDER BY [User_Id],[CreatedDate] DESC)
    FROM YourTable

Solution 3:

select id
,user_id
,row_number() over(partition by user_id order by user_id) as sort_number 
from table

Solution 4:

Use ranking function row_number()

select *, 
        row_number() over (partition by User_id order by user_id, date desc) 
from table t