Search code examples
sqlteradatarankingranking-functions

Case statement with Ranking function

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

enter image description here

Solution

  • 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'.