CODEWITHSUNDEEP
sqlpostgresql
Sujeet Chaurasia
Sujeet Chaurasia

Reputation: 163

CASE STATEMENT IN WHERE CLAUSE in QUERY

My table is as below

ID    |             email                 | ref
-------+-----------------------------------+------------
    12 | [email protected]                 |          0
    12 | [email protected]                 |          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?

Upvotes: 2

Views: 145

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

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);

Upvotes: 1

DhruvJoshi
DhruvJoshi

Reputation: 17126

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

Upvotes: 0

Related Questions