CODEWITHSUNDEEP
sqlwindow-functions
viviboox3
viviboox3

Reputation: 329

MIN() MAX() on a column created using window function

I created a column called custom_rank using window function. Is there a way to get a min and max of that specific column per userid? I'm basically trying to get a min and max weight of a given user based within their enrollment period.

select top 1000 
  c.weight, c.units, 
  rank() over (partition by uuid, enrolled_on order by enrolled_on desc, input_date asc) as "weight_rank"
  from tableA a
join tableB b
  on (b.member_no = a.member_no)
join tableC c
  on (c.userId = b.uuid)
where input_date >= enrolled_on and input_date < cancel_date

Upvotes: 0

Views: 5127

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

Just use max() and min() as window functions:

with cte as (
      select c.weight, c.units, uuid,
             rank() over (partition by uuid, enrolled_on
                          order by enrolled_on desc, input_date asc) as weight_rank
      from tableA a join
           tableB b
           on b.member_no = a.member_no join
           tableC c
           on c.userId = b.uuid
      where input_date >= enrolled_on and input_date < cancel_date
     )
select top 1000 cte.*,
       max(weighted_rank) over (partition by uuid) as max_weightedrank_user,
       min(weighted_rank) over (partition by uuid) as min_weightedrank_user,
from cte;

Upvotes: 0

McNets
McNets

Reputation: 10807

Use a CTE and then get max/min of each user.

with mm as
(
    select top 1000 
      c.weight, c.units, c.userId
      rank() over (partition by uuid, enrolled_on order by enrolled_on desc, input_date asc) as "weight_rank"
      from tableA a
    join tableB b
      on (b.member_no = a.member_no)
    join tableC c
      on (c.userId = b.uuid)
    where input_date >= enrolled_on and input_date < cancel_date
)
select   userId, min(weight_rank), max(weight_rank)
from     mm
group by userId;

Upvotes: 2

Related Questions