Distinct

How do I use a distinct correctly?

[JebbyToast]

I'll start by saying I use SqlPlus.

I get requests for info from our DB, pretty much selects that I throw into Excel Spreadsheets.

So I need to return about 10+ fields and one of the fields is the clients SSN along with name (reg00), Phone#, etc for each client.

I write the query out and it seems I cannot use the distinct function correctly. I try to make the focus the SSN and no matter what I try it keeps giving back dupe SSNs. I know it is thinking that as a whole, the row is distinct, but I need no duplicate SSNs.

The last one I tried went something like this...

select a.ssn0, a.acctnum, a.reg00, a.reg10, a.reg20, a.city0, a.address10, a.address20, a.zip0, a.state0, a.opendate
from accounts a
where a.acctnum in (select distinct accounts.ssn0 from accounts, sponaccount, accountreps

where sponaccount.acctnum = accounts.acctnum

and accountreps.acctnum = accounts.acctnum

and sponaccount.accttrail = accounts.accttrail

and sponaccount.sponcode not in ('X','Y')

and sponaccount.acctnum not in ('999999999','454545454','123456789')

and accounts.zip0 is not null

and accounts.regtype <>'X'

and accounts.opendate < '31-DEC-06')

Oh, and there is no ; because i use Zend.

So, any successful distincters out there?

[edited by: JebbyToast at 3:37 pm (utc) on July 23, 2007]

[phranque]

try adding a " GROUP BY accounts.ssn0" just before the closing parenthesis for the 2nd select.

[JebbyToast]

Thanks for the suggestion, I tried it and it is still giving me distincts of the total of the row. ie. still getting dupe SSNs.

The major problem is that data entered in this DB is not always consistent. One operator will enter in #*$!X W. Circle Pl. while another operator will enter in #*$!X West Circle Pl.
thus making rows distinct from each other that shouldn't be.

I will keep plugging away at this.... thanks for response again.

[phranque]

i'm not sure what your query is really doing, but perhaps you want to specify DISTINCT on the first select rather than the 2nd one...

[stajer]

let me agree with phranque. There is no need for the second select and it is causing your dup ssn problem.

select distinct a.ssn0, a.acctnum, a.reg00, a.reg10, a.reg20, a.city0, a.address10, a.address20, a.zip0, a.state0, a.opendate 
from accounts, sponaccount, accountreps where sponaccount.acctnum = accounts.acctnum 
and accountreps.acctnum = accounts.acctnum and sponaccount.accttrail = accounts.accttrail and sponaccount.sponcode not in ('X','Y') and sponaccount.acctnum not in ('999999999','454545454','123456789') 
and accounts.zip0 is not null and accounts.regtype <>'X' and accounts.opendate < '31-DEC-06'

[phranque]

try this:

select distinct a.ssn0, a.acctnum, a.reg00, a.reg10, a.reg20, a.city0, a.address10, a.address20, a.zip0, a.state0, a.opendate
from accounts a, sponaccount, accountreps
where sponaccount.acctnum = a.acctnum
and accountreps.acctnum = a.acctnum
and sponaccount.accttrail = a.accttrail
and sponaccount.sponcode not in ('X','Y')
and sponaccount.acctnum not in ('999999999','454545454','123456789')
and accounts.zip0 is not null
and accounts.regtype <>'X'
and accounts.opendate < '31-DEC-06'
group by a.ssn0