Stack Overflow Asked by Sujeet Chaurasia on January 31, 2021
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?
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
Answered by DhruvJoshi on January 31, 2021
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);
Answered by Gordon Linoff on January 31, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP