How to use alias name with partition by in sql

Question

I'm fetching record of players having categorized with golf handicaps. Like players having handicap between 0 to 5 lies in 0-5 range and similarly having handicap between 6-11 lies in the range of 6-11 and so on and so forth. What I'm trying is to fetch top 3 players from each range so that I can setup flights for each rounds.

I have used partition by clause to separate records and ROW_NUMBER to get top 3 players from each range. In order to define ranges, i have used multiple cases. Now how do i use range as alias name with partition by or any way that can generate the correct result. Below is my query.

  select * from (
  select  uu.Id, firstname, lastname, userhandicap, 
  case when userhandicap>=0 and userhandicap<=5 then '0-5'
  when userhandicap>=6 and userhandicap<=11 then '6-11' 
  when UserHandicap>=12 and UserHandicap<=18 then '12-18' 
  when UserHandicap>=19 and UserHandicap<=26 then '19-26'
  else '27 and above' end as range, RN = ROW_Number()  over (PARTITION BY 
  range order by cast(userhandicap as int))
  from dbo.[User] uu inner join dbo.[EventRegisteredUsers] eru
  on uu.Id = eru.UserId 
  where eru.UserId not in (Select fp.UserId from dbo.[FlightPlayer] fp 
  inner join dbo.[Flight] f
  on fp.FlightId = f.Id 
  where f.Rounds = '1'
  and f.Starthole = '0a9b926e-0baa-4369-8cf8-8fc84ca80d65' and f.EventId = 
  '7de10ad6-098d-419f-9c2d-2e62803ad1f7')
  and eru.EventId = '7de10ad6-098d-419f-9c2d-2e62803ad1f7') uu
  WHERE 
   uu.RN <= 3 

Answer 1

You can use apply to define the range value within the subquery. This is the simplest method for defining the range:

select *
from (select uu.Id, firstname, lastname, userhandicap, 
             row_number() over (partition by v.range order by cast(userhandicap as int)) as seqnum
      from dbo.[User] uu inner join
           dbo.[EventRegisteredUsers] eru
           on uu.Id = eru.UserId cross apply
           (values (case when userhandicap <= 5 then '0-5'
                         when userhandicap <= 11 then '6-11' 
                         when UserHandicap <= 18 then '12-18' 
                         when UserHandicap <= 26 then '19-26'
                         else '27 and above'
                    end)
           ) v(range)
      where not exists (select 1
                        from dbo.[FlightPlayer] fp join
                             dbo.[Flight] f
                             on fp.FlightId = f.Id 
                        where eru.UserId = fp.UserId and 
                              f.Rounds = '1' and
                              f.Starthole = '0a9b926e-0baa-4369-8cf8-8fc84ca80d65' and
                              f.EventId = '7de10ad6-098d-419f-9c2d-2e62803ad1f7'
                       ) and
            eru.EventId = '7de10ad6-098d-419f-9c2d-2e62803ad1f7'
       ) uu
where uu.seqnum <= 3; 

Note other changes to the query:

• Don't use not in with a subquery. If the subquery returns a NULL value, then all values are filtered out. That is not (usually) the expected behavior.
• The case expression is overly complicated. Use the fact that case is guaranteed to evaluate the conditions in order.
• You should qualify all column names in a query that has more than one query. However, it is unclear where the columns come from.
• Presumably handicap is not ever negative, based on your original logic (and the rules of golf), so I am comfortable removing that condition.