Select unique record if does not belong to group-CodePudding

I want to filter the table in such way that if the City shares the same code should not get selected Please advise how to achieve this.

Code	City
3	MYS
3	NYR
3	STA
4	MYS
4	MYS
5	MYS

Expecting Result

Code	City
4	MYS
5	MYS

CodePudding user response：

In SQL server using an apply operator allows for an efficient form of correlated subquery that can also be referenced in the where clause. Here we can count the distinct number of city values per code, and if this is 1 then output just those rows plus use select distinct to remove any duplicate result rows:

with a as (
    select 3 as code, 'MYS' as City
    union all
    select 3, 'NYR'
    union all
    select 3, 'STA'
    union all
    select 4, 'MYS'
    union all
    select 4, 'MYS'
    union all
    select 5, 'MYS'
    )
select distinct
     a.code
   , a.city
from a
cross apply (
       select count(distinct c.city)
       from a as c
       where a.code = c.code
       ) as ia (c_count)
where ia.c_count = 1

result

 ------ ------ 
| code | city |
 ------ ------ 
|    4 | MYS  |
|    5 | MYS  |
 ------ ------

db<>fiddle here

CodePudding user response：

Can you try if this is what you want

with a as (
    select 3 as code, 'MYS' as City
    union all
    select 3, 'NYR'
    union all
    select 3, 'STA'
    union all
    select 4, 'MYS'
    union all
    select 4, 'MYS'
    union all
    select 5, 'MYS'
)
select *
from (
    select distinct code, city
    from a
) b
group by code
having count(*) = 1;

This was for Mariadb, but the logic is distinct, count(*) and group by. This is one way to do it

For SQL Server try this

Select distinct * from a where code in (select code
from (
select distinct code, city
from a
) b
 group by code
having count(*) = 1);

Fiddle

CodePudding user response：

You can simply group by Code and count(distinct City) = 1. There is only one unique City in the result you may use min or max to display it

select Code, min(City) as City
from   yourtable
group by Code
having count(distinct City) = 1