Search code examples
c#sql-server-2005ranking

How to make and update ranking in database c#


I have this query:

Select [col], DENSE_RANK() Over(ORDER BY col DESC)as rank From Table

I have 2 column, x and y. But i want to update data in column x with result of DENSE_RANK(). Honeslty, i don't know how can that's query work, but when i try, that's work.

But when i tried that's query in query builder, will appear a warning "The OVER SQL construct or statement is not supported." . So i need to make ranking of data in column x, then save the result of ranking into column y.


Solution

  • You can do something like below. Here b and a are table alias. You need to join with the same table -> get the ranking data -> and then update the desired column

    UPDATE a 
    SET y = b.rank
    FROM Table a
    JOIN (
    Select x, DENSE_RANK() OVER(ORDER BY x DESC)as rank From Table ) b
    ON a.x = b.x;