CODEWITHSUNDEEP
sql-server-2005
vovkjn
vovkjn

Reputation: 99

how to take unique values in sql

SELECT DISTINCT CustomerID, ADRESS
FROM A
LEFT JOIN B
ON A.CustomerID=B.CustomerID
WHERE 
Points > 15

So after it gives me the result with customerIDs and adresses, but incase customer may have 2 IDs, for example he or she has twice registered, however address is the same.

How can i take only unique values by adress, so i would like that adress were unique, if there are two different IDs but they have the same adress the sql has to leave only 1 value.

Please help!

Upvotes: 0

Views: 242

Answers (2)

tiko
tiko

Reputation: 61

If I understood your question correctly I would suggest to use a GROUP BY address. By grouping multiple customers by the same address you will run into problems if there are two different customers which are living at the same address. To prevent this you could also add the name of the customer to the group by. It should be fairly unlikely that two people with the same name live at the same address but it still could happen...

Upvotes: 1

Ofer Zelig
Ofer Zelig

Reputation: 17470

SELECT ADRESS, MAX(A.CustomerID) FROM A LEFT JOIN B ON A.CustomerID=B.CustomerID WHERE Points > 15
GROUP BY ADRESS

Upvotes: 0

Related Questions