CODEWITHSUNDEEP
postgresql
rshar
rshar

Reputation: 1477

Filter rows in PostgreSQL table based on column match conditions

I have following table in PostgreSQL 11.0

col1    col2      col3      col4
1       a         a         a          
1       a         a         a_1
1       a         a         a_2
1       b         b         c
2       d         d         c
3       e         d         e

I would like to filter above table such that if col2 and col4 are equal, only this match should be selected and below two rows are excluded. When col2 and col4 are not equal, rows with col2 = col3 should be kept.

The desired output is:

col1    col2      col3      col4
1       a         a         a          
1       b         b         c
2       d         d         c
3       e         d         e

I am trying following query with no success so far.

select * from table1
where col2=col4
union
select * from table1
where col2 !=  col4 and col2=col3

but this will include rows where there is already a match, which I want to exclude in the final output.

1       a         a         a_1
1       a         a         a_2

Upvotes: 1

Views: 878

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246268

I would use

SELECT DISTINCT ON (col2) *
FROM table1
WHERE col2 = col4 OR col2 = col3
ORDER BY col2, col2 IS DISTINCT FROM col4;

This relies on FALSE < TRUE.

Upvotes: 1

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

As per my understanding you want unique col2 in you result with given conditions:

Try this:

with cte as 
(select *, 
 case 
 when col2=col4 then 2 
 when col2=col3 then 1 
 else 0 
 end "flag" from table1 )
 
 select distinct on(col2) col1,col2,col3,col4 from cte where flag>0
order by col2, "flag" desc

Demo on Fiddle

Upvotes: 1

Related Questions