CODEWITHSUNDEEP
c#sql-server-2005ranking
Stev
Stev

Reputation: 33

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.

Upvotes: 0

Views: 221

Answers (1)

Rahul
Rahul

Reputation: 77876

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;

Upvotes: 1

Related Questions