use the case statement and set a different value for every each top 10 records

Question

I have a table that returns a list of records with two-column:

SELECT Id, Duration from dbo.[VW_ActivityDuration] ORDER BY DURATION desc

I want to use the case statement and set a different value for every top 10 records, which is ordered by DURATION in descending.

Example:

set the value as 5 for the first 10 records 

and set the value as 4 for the next 10 records 

and set the value as 3 for the other next 10 records 

and set the value as 2 for the other next 10 records 

similarly, I have to set the value of 1 for all after that

SELECT TOP (1000) [Id]
      ,[Name],

      CASE
        WHEN  ---  THEN '5'
        ELSE '1'
    END AS [Value]

  FROM [EngagementDb].[dbo].[VW_ActivityDuration]
  ORDER BY [DurationMinutes] desc

Any head-start, hints, or suggestions would be appreciated. I couldn't start writing a query to start with this problem.

Answer 1

I think you want row_number() and a case expression:

select id, DurationMinutes,
    case 
        when row_number() over(order by DurationMinutes desc) <= 10 then 5
        when row_number() over(order by DurationMinutes desc) <= 20 then 4
        when row_number() over(order by DurationMinutes desc) <= 30 then 3
        when row_number() over(order by DurationMinutes desc) <= 40 then 2
        else 1
    end as val
from [EngagementDb].[dbo].[VW_ActivityDuration]
order by DurationMinutes desc

We can shorten that a little with arithmetics:

select id, DurationMinutes,
    case when row_number() over(order by DurationMinutes desc) <= 40 
        then 5 - (row_number() over(order by DurationMinutes desc) - 1) / 10
        else 1
    end as val
from [EngagementDb].[dbo].[VW_ActivityDuration]
order by DurationMinutes desc