Filter rows in PostgreSQL table based on column match conditions

Question

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

Akhilesh Mishra · Accepted Answer · 2020-07-13 01:59:14Z

1

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

edited Jul 13, 2020 at 1:59

answered Jul 13, 2020 at 1:37

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Laurenz Albe · Accepted Answer · 2020-07-13 05:58:56Z

1

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.

answered Jul 13, 2020 at 5:58

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

Filter rows in PostgreSQL table based on column match conditions

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related