How to filter data with distinct several column values

user_7810554 0
Viewed 36

Have table with values

create table test_tbl (id serial,create_date timestamptz,display_name text,address text);

insert into test_tbl(create_date,display_name,address)
values (NOW(),'name1', 'addr1'),(NOW(),'name1', 'addr1'),(NOW(),'name1', 'addr1'),
(NOW(),'name2', 'addr2'),(NOW(),'name2', 'addr2'),(NOW(),'name3', 'addr2'),
(NOW(),'name3', 'addr3'),(NOW(),'name4', 'addr3'),(NOW(),'name1', 'addr3'),
(NOW(),'name5', 'addr5');

The goal is to filter clients which are alone on address (even if client go several times), but if same client go several times in specific address and there is other clients also I need that values.

Result of query must be like :

id  create_date  display_name  address
4  2020-12-01  name2  addr2
5  2020-12-01  name2  addr2
6  2020-12-01  name3  addr2
7  2020-12-01  name3  addr3
8  2020-12-01  name4  addr3
9  2020-12-01  name1  addr3

We must filter data with ID 1,2,3 because there is one unique client on specific address and id = 10 because he is there alone

subquery where-clause postgresql sql
2 Answers

You can use exists to retain only rows for which another row exists with the same address and a different display_name.

select *
from test_tbl t
where exists (
    select 1 from test_tbl  
    from test_tbl t1 
    where t1.adddress = t.address and t1.display_name <> t.display_name
)

Demo on DB Fiddle:

id | create_date                   | display_name | address
-: | :---------------------------- | :----------- | :------
 4 | 2020-12-01 18:42:26.651971+00 | name2        | addr2  
 5 | 2020-12-01 18:42:26.651971+00 | name2        | addr2  
 6 | 2020-12-01 18:42:26.651971+00 | name3        | addr2  
 7 | 2020-12-01 18:42:26.651971+00 | name3        | addr3  
 8 | 2020-12-01 18:42:26.651971+00 | name4        | addr3  
 9 | 2020-12-01 18:42:26.651971+00 | name1        | addr3
user_106767160

With count(distinct display_name) you will find how many distinct display_names there are on one address. Then with having count(distinct display_name) > 1 you remove them. And with having count(distinct display_name) = 1 you select only those addresses that have one display_name.

select * 
from test_tbl
where address in (select address
                  from test_tbl
                  group by address
                  having count(distinct display_name)  > 1)

Here is a demo:

DEMO

user_65650380
Related