CASE STATEMENT IN WHERE CLAUSE in QUERY

user_4049453 0
Viewed 78

My table is as below

ID    |             email                 | ref
-------+-----------------------------------+------------
    12 | test@testmail.com                 |          0
    12 | test@testmail.com                 |          1

Now the requirement of the query is to retrieve the ID and the email where the reference is 0, however, if the email is null with ref 0 then it should take the email value of ref 1. If both ref has the email value then by default it should take 0.

I tried with the below query but it fails, it is giving me both the value

select 
    id, 
    email, 
    ref 
from 
    table1 
where ref in (case when email is not null then 0 else 1 end) and ID=12;

Is there any way to achieve this?

postgresql sql
2 Answers

Use distinct on:

select distinct on (email) t1.*
from table1 t1
order by email, ref asc;

This returns one row per email. The row returned is the one with the smaller value of ref.

Note: distinct on is a convenient Postgres extension to the SQL standard. It is not available in other databases.

EDIT:

I think you may want one row per id. If so:

select distinct on (id) t1.*
from table1 t1
order by id,
         (case when ref = 0 and email is not null then 1
               when ref = 1 then 2
               else 3
          end);
user_11440350

using your logic in mind below should give you the expected output

; with cte as 
(
select id, email, rn=row_number() over (partition by id order by ref asc) 
from table1 
where (email is NOT NULL OR ref=1) 
)
select id, email from cte
where rn=1
user_11232260
Related