CODEWITHSUNDEEP
postgresql
rshar
rshar

Reputation: 1477

How to select rows from table based on specific filters in PostgreSQL 11.0

I have following table in PostgreSQL 11.0

col1            col2          col3                         col4     col5             col6                      
98262898184 NCT01243502       0.01% ct327 (or placebo)     false    placebo term    (null)
98262898184 NCT01243502       0.01% ct327 (or placebo)     false    no mapping      (null)
98262898185 NCT01268527       0.01% e6201                  false    no mapping      (null)  
98262898165 NCT01654484       0.0015% tafluprost           true     (null)          (null)
98263196888 NCT02925793       vehicle cream                false    vehicle term    (null)
98263196888 NCT02925793       vehicle cream                false    no mapping      (null)

I would like select those rows where for a common values of col1, col2, col3 and col5 value is placebo term, vehicle term and excluding is counter row.

The desired table is:

col1            col2          col3                         col4     col5             col6                      
98262898184 NCT01243502       0.01% ct327 (or placebo)     false    placebo term    (null)
98262898185 NCT01268527       0.01% e6201                  false    no mapping      (null)  
98262898165 NCT01654484       0.0015% tafluprost           true     (null)          (null)
98263196888 NCT02925793       vehicle cream                false    vehicle term    (null)

I have no idea at the moment how to get this desired output. Any help is highly appreciated.

Upvotes: 0

Views: 33

Answers (1)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

What I understood from your question is you want distinct rows from your table based on col1, col2, col3 and prefer the row having col5 value as placebo term or vehicle term.

Try this:

with cte as (
     select * , 
           case when col5='placebo term' or col5='vehicle term' 
           then 1 else 0 
           end as "flag_" 
     from my_table
)

select distinct on (col1,col2,col3) 
               col1, col2, col3 col4, col5, col6 
from cte 
order by col1, col2, col3, flag_ desc

DEMO

Upvotes: 2

Related Questions