CODEWITHSUNDEEP
sqlsql-server
user3183055
user3183055

Reputation: 83

How to select only distinct fields

Let's say I have a table of customer addresses:

CName           |   AddressLine
-------------------------------
John Smith      | 123 Nowheresville
Jane Doe        | 456 Evergreen Terrace
John Smith      | 999 Somewhereelse
Joe Bloggs      | 1 Second Ave

I want to pick CName with distinct AddressLine means i dont want to pick "John Smith " as it has two addresses. How do i do that?

Upvotes: 0

Views: 83

Answers (4)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Use:

SELECT CName
FROM Addresses
GROUP BY CName
HAVING COUNT(DISTINCT AddressLine) = 1

Upvotes: 1

Joe Taras
Joe Taras

Reputation: 15379

Try this:

SELECT a.CName
FROM Addresses a
GROUP BY a.CName
HAVING COUNT(a.AddressLine) = 1

Upvotes: 0

Siyual
Siyual

Reputation: 16917

Alternatively, you could use WHERE NOT EXISTS:

Select  CName, AddressLine
From    YourTable   A
Where Not Exists
(
    Select  *
    From    YourTable   B
    Where   A.CName = B.CName
    And     A.AddressLine <> B.AddressLine
)

Edit to address performance:

Create Table Test (CName Varchar (20), AddressLine Varchar (50));

Insert Test Values 
('John Smith', '123 Nowheresville'),
('Jane Doe', '456 Evergreen Terrace'),
('John Smith', '999 Somewhereelse'),
('Joe Bloggs', '1 Second Ave')


select cname, min(AddressLine) as AddressLine
from test
group by cname
having count(*) = 1;

Select  CName, AddressLine
From    Test   A
Where Not Exists
(
    Select  *
    From    Test   B
    Where   A.CName = B.CName
    And     A.AddressLine <> B.AddressLine
);

Execution plans:

enter image description here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You can get the distinct rows by doing:

select cname, min(AddressLine) as AddressLine
from t
group by cname
having count(*) = 1;

Upvotes: 1

Related Questions