PostgreSQL Issue in Case Priority query

Question

I am using PostgreSQL. I am stuck in building the query. How can we search the result by giving priority to data and discard rest of the data if the any of the priority matched the cases?

Grouping by family wise

If a single person in family has commitment level “VOTER” then it should be fetched rather than other person in the same family whose status are different such as “COMMITTED”, or “REACHOUT”, or “NEUTRAL”

If a single person in the family has commitment level “COMMITTED” then it should be fetched rather than other person in the same family whose status are different such as “REACHOUT” or “NEUTRAL”

This hierarchy follows as “VOTER”, “COMMITTED”, “REACHOUT”, “NEUTRAL” If all the person in the family has the same committed level then the person mobile number if not null should be fetched.

Other are discarded

Sample table with data

ID  COMMITTED LEVEL MOBILE NUMBER   FAMILY NUMBER
1   VOTER               234828288       1
2   COMMITED            262349911       1
3   COMMITED            924792922       2
4   REACHOUT            82639472        2
5   VOTER               79246826234     3
6   VOTER               NULL            3

OUTPUT: result expected

ID  COMMITED LEVEL      MOBILE NUMBER   FAMILY NUMBER
1   VOTER               234828288       1
3   COMMITED            924792922       2
5   VOTER               79246826234     3

Don't add tags for products not involved...

jarlh
– jarlh

2018-05-15 12:07:39 +00:00
Commented May 15, 2018 at 12:07 — jarlh
– jarlh, Commented May 15, 2018 at 12:07

Gordon Linoff · Accepted Answer · 2018-05-15 12:14:03Z

3

You can do this using distinct on:

select distinct on (family_number) t.*
from t
order by family_number,
         (case when committed_level = 'Voter' then 1
               when committed_level = 'COMMITTED' then 2
               when committed_level = 'REACHOUT' then 3
               when committed_level = 'Neutral' then 4
               else 5
          end),
         (case when mobile_number is not null then 1 else 2 end);

answered May 15, 2018 at 12:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL Issue in Case Priority query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related