How to get unique field

Question

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

I want to pick CName with unique AddressLine means i dont want to pick "John Smith " and "John Black" as they have same addresses. How do i do that?

So, you exclude both rows or you need one of them? In that case, wich john? — Horaciux, Nov 02 '16 at 21:07

score 2 · Answer 1 · answered Nov 02 '16 at 20:53

For all the non null addresses you can group by the addressline and select only those which are unique.

select * from t
where addressline in (select addressline from t 
                      where addressline is not null 
                      group by addressline 
                      having count(*) = 1)
--or addressline is null

or

select cname,addressline
from (select t.*, count(*) over(partition by addressline) cnt
      from t) x 
where cnt = 1

ScaisEdge · Answer 2 · 2016-11-02T21:11:58.630

1

You can use not in subselect with group by and having

select * from my_table
where AddressLine not in (select AddressLine from my_table 
                           group by AddressLine  
                           where AddressLine  is not null
                           having count(*) >1);

edited Nov 02 '16 at 21:11

answered Nov 02 '16 at 20:54

ScaisEdge

131,976
10
91
107

this won't work if there are multiple null values in addressline column. – Vamsi Prabhala Nov 02 '16 at 21:07

Kamil Gosciminski · Answer 3 · 2016-11-02T21:59:33.610

0

Use EXISTS with grouping and costraint the output of it where an addres is unique with having clause:

select *
from yourtable t1
where exists (
  select addressline
  from yourtable t2
  where t1.addressline = t2.addressline
  group by addressline
  having count(*) = 1
  )

edited Nov 02 '16 at 21:59

answered Nov 02 '16 at 20:52

Kamil Gosciminski

16,547
8
49
72

this will not give the desired results because the sub query in EXISTS is not related. so if left as it is it would say if any addressline only has a count of 1 return the record. Not if the addressline on that row..... – Matt Nov 02 '16 at 21:08

score 0 · Answer 4 · edited May 23 '17 at 10:32

Nearly the same answer as VKP for the COUNT(*) over but I prefer it as a common table expression.

;WITH cte AS  ( 
    SELECT
       *
       ,COUNT(*) OVER (PARTITION BY AddressLine) as RecordsAtAddress
    FROM
       TableName
)

SELECT *
FROM
    cte
WHERE
    RecordsAtAddress = 1

However, I did want to add ROW_NUMBER window function to show you how you can always select a record per AddressLine. So in this case you would get 1 of the duplicate records instead of excluding them all together.

;WITH cte AS (
    SELECT *
       ,ROW_NUMBER() OVER (PARTITION BY AddressLine ORDER BY CName) as RowNum
    FROM
       TableName
)

SELECT *
FROM
    cte
WHERE
    RowNum = 1

Be Vary Cautious of using IN or NOT IN especially with free form text like this because if AddressLine can be NULL you will NOT get what you expect! Also performance is typically not as good. NOT IN vs NOT EXISTS

For the EXISTS or NOT EXISTS answer you will actually have to relate your sub query something like this, note NOT EXISTS may perform better for a case like this:

SELECT *
FROM
    TableName t1
WHERE
    NOT EXISTS (SELECT
                t2.AddressLine
             FROM
                TableName t2
             WHERE
                t1.AddressLine = t2.AddressLine
             GROUP BY
                t2.AddressLine
             HAVING COUNT(*) > 1)

How to get unique field

4 Answers4