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

trincot · Accepted Answer · 2017-08-22T22:53:05.110

11

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

edited Aug 22 '17 at 22:53

answered Aug 22 '17 at 22:46

trincot

317,000
35
244
286

1

Thank you @Trincot this was very useful and helped answer my question! – doraav Aug 23 '17 at 14:29

score 2 · Answer 2 · answered Aug 23 '17 at 08:07

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.

answered Aug 23 '17 at 08:07

dnoeth

59,503
4
39
56

thanks for help this seemed to work best for what I needed. Appreciate it. – doraav Aug 23 '17 at 14:30
This worked for me, but I had to name the new column as an alias. – Dudelstein Jan 21 '21 at 09:40

Victor Hugo Terceros · Answer 3 · 2017-08-22T23:23:05.183

0

What I suggest is a Partition by your Group column and then Rank by Age

---------------Test Table with data---------
declare @tbl table(age int, policy varchar(20))
insert into @tbl
select 1, 'A' union
select 12, 'A' union
select 20, 'A' union
select 19, 'B' union
select 30, 'B' union
select 11, 'B' union
select 4, 'C' union 
select 14, 'C' union
select 5, 'B' union
select 16, 'D'
---------------Main Query--------------------
select policy,age, 
'1' as Under21, 
rank() over (partition by policy order by age desc) as Rank_Under_21 
from @tbl
where age <21
union
select policy,age, 
'0' as Under21, 
 0 as Rank_Under_21 
from @tbl
where age >=21
order by policy asc,age desc

edited Aug 22 '17 at 23:23

answered Aug 22 '17 at 22:46

Victor Hugo Terceros

2,969
3
18
31

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 Aug 22 '17 at 23:04
I updated my query, it gets the exact result you expect as you can see in the image – Victor Hugo Terceros Aug 22 '17 at 23:24

score 0 · Answer 4 · answered Aug 22 '17 at 23:05

The below code is for MS SQL Server. Thanks to @Victor Hugo Terceros, for sample code.

DECLARE @tbl TABLE
  (
     age INT,
     grp VARCHAR(20)
  )

INSERT INTO @tbl
SELECT 1,
       'A'
UNION
SELECT 12,
       'A'
UNION
SELECT 20,
       'A'
UNION
SELECT 19,
       'B'
UNION
SELECT 30,
       'B'
UNION
SELECT 11,
       'B'
UNION
SELECT 4,
       'C'
UNION
SELECT 14,
       'C'
UNION
SELECT 5,
       'B'
UNION
SELECT 16,
       'D'

SELECT grp     AS Policy,
       age,
       under21 AS Under21,
       CASE
         WHEN under21 = 0 THEN Dense_rank()
                                 OVER(
                                   partition BY grp
                                   ORDER BY under21age DESC)
         ELSE 0
       END     AS Rank_Under_21
FROM   (SELECT CASE
                 WHEN age < 21 THEN 0
                 ELSE 1
               END AS Under21,
               CASE
                 WHEN age < 21 THEN age
                 ELSE 0
               END AS under21age,
               age,
               grp
        FROM   @tbl) AS t

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. — doraav, Aug 22 '17 at 23:11
Can you please edit your question and provide the desired result set. — Venkataraman R, Aug 22 '17 at 23:15

Case statement with Ranking function

4 Answers4