I have a table 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:
I have tried some query :
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
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.