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 want a new table with the two new fields i.e the gold and silver fields.
i want the output to be something like this:
Please help me with the query or give me some suggestions on how to proceed.
Thanks a lot.
I think you want to use
dense_rank() for this:
select t.*, (case when rnk = 1 then 1 else 0 end) as gold, (case when rnk = 2 then 1 else 0 end) as silver from (select t.*, dense_rank() over (partition by week order by pointsrewarded) as rnk from t ) t;
dense_rank() will handle the case when there are ties. In that case, multiple "gold" and "silver" values will be assigned.
I should also note that the subquery is not necessary. You can repeat the
dense_rank() in the outer query. I just think it is easier to follow the logic this way.