I have two tables. One with a policy name and one with member details. I need to identify the policy name with least amount of members and want to use a rank function but it isnt working for me. Getting strange result. Sample of the results can be seen below. The results coming back for the count are not correct. It should be in the thousands and not in the low numbers it is giving.
Strangely when I put rank in desc order it returns the correct count amount. What am I doing wrong here. I want to get it so that I can pick lowest by selecting rank = 1 and show the correct count amount
select count (distinct member_id) as count_members,
policy_name,
rank () over (order by count(distinct member)) as rank
from member_table t1, policy_table t2
where t1.policy_id = t2.policy_id
and month_id = 202210
group by policy_name
| count_members | policy_name | rank |
|---|---|---|
| 3 | Policy a | 1 |
| 5 | Policy b | 2 |
