You can try this:



SELECT noteid,folderid,subject

FROM dbnotes

WHERE (subject In (SELECT subject FROM dbnotes GROUP BY subject HAVING
Count(*)>1 ))



Regards,

Paul





From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of ifitsx
Sent: Friday, February 08, 2008 2:01 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: duplicate string fields?



Set,

Thanks for your reply.

I use this technology to support my research, and as such I sometimes
spend as much as a year or two away from having to think about SQL or
anything related, so I realize I may be taxing people's patience with
forgotten fundamentals.

> Your error is extremely simple - you are trying to use a DSQL component
> for selecting a result set, and a DSQL component can only return

zero or

> one row. Basically, you should use DSQL for things like CREATE, INSERT,
> UPDATE etc. whereas you should use a CURSOR component for things that
> return result sets like SELECT.
>
> So, use the Cursor tab rather than the DSQL tab and Anns statement will
> work.
>
> This helps,
> Set

It did finally occur to me that Dynamic SQL only works for one row,
however, I still did not realize that the Cursor dialog in IB_SQL was
the place for this.

Still the following script only returned one of the duplicate records:

select a.noteid, a.folderid, b.noteid, b.folderid, a.subject
from dbnotes a
join dbnotes b on (a.subject = b.subject)
where a.noteid < b.noteid

However, that was enough to point me toward one pair that I needed to
evaluate, and begin my housecleaning.

The restriction imposed by a.noteid < b.noteid missed those records
where subject is duplicated and a.noteid > b.noteid.

Changing the script to where a.noteid < b.noteid or a.noteid >
b.noteid effectively returns only where a.noteid > b.noteid and misses
the actual record(s) where where a.noteid < b.noteid.

I would appreciate the opportunity to totally complete this SQL/IB_SQL
lesson - if I haven't exhausted everyone's patience yet - and find out
if there is a script that will return both _pairs_ of records, instead
of returning either one of the pair of records where a.noteid <
b.noteid and implies there is another with a matching subject, or one
of the pair of records where a.noteid > b.noteid and implies there is
another with a matching subject.

Is there a script to return both pairs, i.e., all four records?





[Non-text portions of this message have been removed]