It has nothing to do with the DISTINCT keyword if you are referencig rows by
RDB$DB_KEY.

The simpliest and safest solution (IMHO) would be:

DELETE FROM table
WHERE RDB$DB_KEY <> (
SELECT FIRST 1 RDB$DB_KEY
FROM table
WHERE idnum = :id
AND stcknum = :stknum
AND qty = :qt
)
AND idnum = :id
AND stcknum = :stknum
AND qty = :qt

The above query should delete all but one record for a given {idnum, stcknum, qty}
tuple.

Regards,
Daniel Berstein.

On 29 Aug 2003 at 21:49, Dixon Epperson wrote:

> > What is it that you are trying to accomplish with having the same
> data in a table
> > multiple times?
> >
>
> the duplicate rows are not part of the design, its an order entry
> module and their customers will often times order an item, and then
> later in the invoice, have it again for some other reason.
>
> This is not a critical table, later in the program flow we
> consolidate, such as when making the invoice. the idea here is not
> to have duplicates, but rather to make the table as easy to work with
> as possible and fast. For that reason, its possible for the order
> entry person to duplicate an entry when not needing to.
>
> I left out the key word DISTINCT in my original question. but I'm
> not sure how to word this. Actually, its not a big deal and short of
> a simple and obvious answer, that would be dependable in future
> versions, I'm not going to mess with it.
>
> Thanx
>
> Dixon Epperson
>
>
>
>
>
>
> To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>