Shivam Sharma Shivam Sharma - 2 months ago 12
SQL Question

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.

Answer

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.