skip to Main Content

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      |

2

Answers


  1. 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. 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