CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
Shivam Sharma
Shivam Sharma

Reputation: 107

reward points and allocate those points in the original table's field

I have a table here : enter image description here

I want to reward a gold and a silver(i.e a value of 1 wherever applicable,else 0 )to top 2 persons by looking at pointsRewarded field.

I already have the first table created .I have modified it with the extra fields that I want i.e rank,gold,silver fields.It has null values now.

i want the output to be something like this: enter image description here

I have tried some query :

update dbo.original
set rnk = dense_rank() over (partition by WeekNumber order by   pointsrewarded desc)

set gold =
   case when rnk = '1' then 1 else 0 end 
set silver =
   case when rnk = '2' then 1 else 0 end

I already have modified the table design by adding the rnk,gold and silver fields.I want the values generated by the query to go and sit in those fields.

Please help me with the query or give me some suggestions on how to proceed.Please.

Thanks a lot.

Upvotes: 0

Views: 245

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

This seems like a follow-up to your earlier question.

In SQL Server you can use an updatable CTE:

with toupdate as (
      select o.*,
             dense_rank() over (partition by WeekNumber order by   pointsrewarded desc) as new_rnk
      from dbo.original
     )
update toupdate
set rnk = new_rank,
    gold = (case when rnk = 1 then 1 else 0 end),
    silver = (case when rnk = 2 then 1 else 0 end);

Note: Only use single quotes for string and date constants. Do not use single quotes for numeric constants.

Upvotes: 1

Related Questions