Case statement with Ranking function

Question

Hello looking for help with ranking.

I'm working with Teradata using SQL and I'm trying to rank a list by a specific group and then by age.

For example: I want to rank by group then only rank those under the selected group that are under 21 years old.

However, when I use the query below it seems to not take into account the members in a group and assigns only if they meet the criteria in the case statement.

select
policy, 
age, 
case when age <'21' then  '1'else '0' end as Under21,
case when age <'21' then dense_rank () over (order by group, age desc)  else '0' end as Rank_Under_21
from   Table

Solution 1

You can use the partition by clause:

dense_rank () over (partition by policy, case when age < 21 then 1 end
                    order by group, age desc)

NB: If age is a numerical field (it should be), then don't compare it with a string: leave out the quotes. If age is of a string type, then be aware that the comparison with another string will be alphabetical, and thus '9' > '21'.

Solution 2

Your code still ranks all ages, the CASE simply replaces the high age ranks with zero.

Another solution (besides to @trincot's answer) which moves the CASE into the RANK:

CASE
  WHEN age < 21 
    THEN Rank ()
         Over (PARTITION BY policy
               ORDER BY CASE WHEN age < 21 THEN age END DESC)
  ELSE 0
END

This also ranks all ages, but the high ones are sorted last und thus got a high rank, which is replaced by the outer CASE with zero.

Comments

[doraav]

Thanks Victor. What I'm looking to do is Count how many members are younger than 21yrs old by Group. And if they do have rows where the age is under 21 than rank them.

[doraav]

Thanks @Victor. I have a table with different groups, ages associated with that group. I'm trying to rank by group those rows where the age is less than 21. For example: Group A - 63 Rank: 0 Group A - 54 Rank: 0 Group A - 19 Rank: 1 Group A - 18 Rank: 2 Group B - 65 Rank: 0 Group B - 50 Rank: 0 Group B - 19 Rank:0 Group B - 8 Rank: 1 I tried your suggestion and it works except that seems it only ranks when it meets the case statement criteria and appears to be the row number that gets listed for the selected group.

[Venkataraman R]

Can you please edit your question and provide the desired result set.

[Victor Hugo Terceros]

I updated my query, it gets the exact result you expect as you can see in the image

[doraav]

Thank you @Trincot this was very useful and helped answer my question!

[doraav]

thanks for help this seemed to work best for what I needed. Appreciate it.

[Dudelstein]

This worked for me, but I had to name the new column as an alias.