Question 88865 in PostgreSQL Question posted in
The official documentation can be found here.

Either or Condition in Postgres SQL – Postgresql

How do I select the rows that have either X or Y in column C but not both in Postgres? Basically there should be no duplicate values in Column B.

Data :

     A      |      B      |     C      |
----------------------------------------
     1      |    John     |     X      |
----------------------------------------
     2      |    John     |     Y      |
----------------------------------------
     3      |    Sam      |     X      |
----------------------------------------
     4      |    May      |     Y      |

Result:

     A      |      B      |     C      |
----------------------------------------
     1      |    John     |     X      |
----------------------------------------
     3      |    Sam      |     X      |
----------------------------------------
     4      |    May      |     Y      |
Tags:

2

Answers


  1. 0

    You can use DISTINCT ON clause:

    select distinct on (b)
a, b, c
from data
order by b, a

    In that case you will get the first row within every unique B value.

    You can also use WINDOW FUNCTIONS or GROUP BY clause if filtering with special conditions is required.

    Login or Signup to reply.
  2. 0

    First, you can count occurencies of ‘X’ and ‘Y’ for the every name (this is what x_or_y does) and then just pick those with count = 1

    with x_or_y as (
select a, b, c, 
       count(case when upper(c) in ('X', 'Y') then 1 else 0 end) over (partition by b order by a) xy_cnt
  from test_t
 )
 
select a, b, c
  from x_or_y
 where xy_cnt = 1;

    enter link description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search