Not displaying Duplicates

Question

I have results displaying duplicates that I don't want. We have a column call addresstypes and it returns a B or L depending on what was entered in db. It was incorrect to enter data if B was chosen as this was both delivery and legal address.

When pulling data, I get serial numbers etc but I get some twice...the ones that have address data in both B & L.

Here is my query - how can I make the double row not display?

USE inventory
SELECT distinct
dbo.addressinfo.locationinfoid, dbo.equipmentlocationscurrent.serialnum,    dbo.addressinfo.addresstype
FROM dbo.equipmentlocationscurrent
full join dbo.addressinfo
on  dbo.equipmentlocationscurrent.locationinfoid = dbo.addressinfo.locationinfoid
where  (clientName = 'cps lease')
and (locationtype = 'merchant')
and (addresstype = 'b' or addresstype = 'l')
order by serialnum

Sample of results

locationinfoid  serialnum
2887540       301-252-800   B
2887540       301-252-800   L

If you want to get distinct `locationinfoid` then, Which `serialnum` and `addresstype` do you want to display from the duplicated rows for each `locationinfoid`?? — Mahmoud Gamal, Mar 15 '13 at 15:07
they are the same locationinfoid and serialnum...I only one either — jlongpre, Mar 15 '13 at 15:08
http://stackoverflow.com/questions/2720830/how-to-select-distinct-in-sql-server-2008-but-for-only-one-field-out-of-many — PM 77-1, Mar 15 '13 at 15:11

score 1 · Answer 1 · answered Mar 15 '13 at 15:10

As per your comment, since it doesn't matter which values of addresstype do you want to select for each locationinfoid, use a GROUP BY locationinfoid, serialnum with MAX:

SELECT
  a.locationinfoid, 
  e.serialnum, 
  MAX(a.addresstype)
FROM dbo.equipmentlocationscurrent AS e
full join dbo.addressinfo AS a on e.locationinfoid = a.locationinfoid
where clientName = 'cps lease'
 and locationtype = 'merchant'
 and addresstype = 'b' or addresstype = 'l'
GROUP BY a.locationinfoid, e.serialnum
order by serialnum;

This will give you distinct values of locationinfoid.

score 0 · Answer 2 · answered Mar 15 '13 at 15:08

Distinct returns any distinct records over any column that is included in the select list. So if you have different values for addressinfo.locationinfoid, serialnum or addressinfo.addresstype then you're going to get duplicate records

In this case it looks like you're pulling in two different addresstypes so you will get 2 "duplicate" records if a locationinfoid and serialnum pair have one of each. Your edit to include the return values confirms this.

If you don't care about the addresstype then don't include it in the select list. Try:

USE inventory
SELECT distinct
dbo.addressinfo.locationinfoid, dbo.equipmentlocationscurrent.serialnum
FROM dbo.equipmentlocationscurrent
full join dbo.addressinfo
on  dbo.equipmentlocationscurrent.locationinfoid = dbo.addressinfo.locationinfoid
where  (clientName = 'cps lease')
and (locationtype = 'merchant')
and (addresstype = 'b' or addresstype = 'l')
order by serialnum

Hector Sanchez · Answer 3 · 2013-03-15T15:19:17.287

You have many options,

Exclude the Address Type from your result and use distinct
use an aggregate funciton and group by function
Make an Intersect Union of your clausules ofr knowing the duplicates

If you don't want to fetch the address type and you wanna know the duplicates, I would rewrite the query in this way:

SELECT     A.locationinfoid, E.serialnum
FROM dbo.equipmentlocationscurrent E
inner join dbo.addressinfo A
on  E.locationinfoid = A.locationinfoid
where  (clientName = 'cps lease')
and (locationtype = 'merchant')
and (addresstype = 'b')
order by serialnum

INTERSECT

SELECT     A.locationinfoid, E.serialnum
FROM dbo.equipmentlocationscurrent E
inner join dbo.addressinfo A
on  E.locationinfoid = A.locationinfoid
where  (clientName = 'cps lease')
and (locationtype = 'merchant')
and (addresstype = 'L')
order by serialnum

If you just want to fetch them all don't include the AddressType in your select using distinct

 SELECT  DISTINCT   A.locationinfoid, E.serialnum
    FROM dbo.equipmentlocationscurrent E
    FULL join dbo.addressinfo A
    on  E.locationinfoid = A.locationinfoid
    where  (clientName = 'cps lease')
    and (locationtype = 'merchant')
    and (addresstype IN('b', 'l'))
    order by serialnum

Also you can use an aggregate function like MAX, or MIN to your clausule, this way you dont need to use your distinct clausule

SELECT  A.locationinfoid, E.serialnum, MAX(A.addresstype ) AS addresstype 
        FROM dbo.equipmentlocationscurrent E
        FULL join dbo.addressinfo A
        on  E.locationinfoid = A.locationinfoid
        where  (clientName = 'cps lease')
        and (locationtype = 'merchant')
        and (addresstype IN('b', 'l'))
        Group by  A.locationinfoid, E.serialnum
        order by serialnum

Not displaying Duplicates

3 Answers3