Return rows if data exists based on a given priority in SQL

Question

I am trying to form a PostgreSQL statement that returns a customer email based on the email type with a given priority. Below I have a table with customers 1 and two. Customer 1 has both personal and company emails whereas customer 2 has on the company.

The problem I am trying to solve is returned the customers personal email if it exists first and if not return the company. So, the personal email is given priority over the company. Is this even possible in PostgreSQL?

 customers
+------------+
| cusomterID |
+------------+
| 1          |
| 2          |
+------------+

customer_email
+------------+-------------+
| cusomterID | email_type  |
+------------+-------------+
| 1          | personal    | -- 0
| 2          | company     | -- 1
| 1          | company     | -- 1
+------------+-------------+

What I am trying now is not really working. It returns all of the rows and does not filter

SELECT *
FROM customers cs
JOIN cumstomer_email cm ON cm.customerId = cs.customreId
WHERE COALESCE(cm.email_type,0) IN (0,1)

sgeddes · Accepted Answer · 2015-07-20 13:54:29Z

2

One option would be to use conditional aggregation:

select customerId, max(case when email_type = 'personal' then email_type
                       else email_type 
                       end) email_type
from customer_email
group by customerId

SQL Fiddle Demo

And here's another option using row_number():

select customerId, email_type
from (select *, 
           row_number() over (partition by customerId 
                              order by email_type = 'personal' desc) rn
      from customer_email) t
where rn = 1

More Fiddle

edited Jul 20, 2015 at 13:54

answered Jul 20, 2015 at 13:51

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

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

1 Comment

user1026498 Over a year ago

I'll give this a try. Thank you!

paul · Accepted Answer · 2015-07-20 13:52:28Z

0

You could do this with a common table expression (CTE):

with emailPriority as (
    select customerId,
           max(email_type) emailType
    from   customer_email
    group by customer_id)
select  cs.*, cm.email_address
from    customers cs join emailPriority ep on cs.customerId = ep.customerId
        join customer_email cm on cm.email_type = ep.email_type

answered Jul 20, 2015 at 13:52

paul

22.1k1 gold badge56 silver badges54 bronze badges

Collectives™ on Stack Overflow

Return rows if data exists based on a given priority in SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related