RE: SQL question

I misread the original message.
Couldn't you take Rob's original example and only select those records that have a count of 1?

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Grizzly Malchow
Sent: Tuesday, March 31, 2009 1:25 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL question

Try adding
ORDER BY key
at the end of the sql statement.


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Lance Gillespie
Sent: Tuesday, March 31, 2009 12:59 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL question

Thank you, Rob. When I run your suggestion, I get:

ACCOUNT ADDRESS COUNT ( ACCOUNT
11 11 MAIN 2
13 12 MAIN 1
12 12 MAIN 1
14 13 MAIN 1
16 15 MAIN 2
15 15 MAIN 1
17 16 MAIN 1
18 17 MAIN 1

and it is not clear to me how I would know from this result
that the 1st, 4th, 7th, and 8th pairs were good and
the rest were bad.

(Record 6 in my sample table is wrong - should be 15 Main)

I think maybe this is not an SQL question. Maybe RPG does
have a reason to exist!

Thanks again,
Lance

<rob@xxxxxxxxx> 3/31/2009 10:18 AM >>>

select account, address, count(account)
from mytable
group by account,address

Rob Berendt