Adam,

> I have the following scenario that although I can solve it two ways so
> far, I am not happy with the plan it generates.
>
> TableA
>
> ID FieldA FieldB
> -- ------ ------
> 1 1 2
> 2 1 2
> 3 3 4
> 4 1 2
> 5 3 5
> 6 3 5
> 7 1 7
>
> Basically, I need to get all records with duplicated FieldA, FieldB
> combinations.
>
> The query will end up in a PSQL for select block.
>
> I will then remove these records, and insert a new record for this
> combination. (for example, it would remove records 1,2 and 4 and
> insert record ID 8 with 1,2).
>
> (This is a notification table)
>
> I can do the following:
>
> select FieldA, FieldB
> from TableA
> group by 1,2
> having count(*)>1
>
> And get the right records, but I don't really need to visit and count
> each of the records (I just need to know if at least one other
> matching record exists).
>
> I have tried several variations of joining back to itself and using
> exists clauses but they still need to do a natural read of TableA, and
> if it is going to do that, then the count(*) is just as efficient.

Is this an operation that needs to be done regularly, or a one-time
operation to clean up the table?

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com