Thank you, you are fantastic! (for a split second :) )

I always wanted to be able to use a reference to the outer select
targets in an inner select, but somehow It never worked and I gave up
quite easily. That's why I usually conjure up wild sql statements.
Your SQL cleared up things a lot and helped

John

--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

>
> Hi John!
>
> Having one sql statement and then put some other sql statement around
> that sql statement quickly become complex, and should be done with

great

> care. I don't know whether your statement should work or not, but if
> you're trying to do what I think you're trying to do, it can certainly
> be simplified considerably:
>
> delete from zaras_futar_forgalmak z1
> where exists(
> select * from zaras_futar_forgalmak z2
> where z2.datum = z1.datum
> and z2.id > z1.id)
>
> This should be a lot quicker than your original sql, it only has to do
> 2000-5000 lookups in your table - your sql did at least a few millions!
>
> However, it may be slightly different from your original sql: It will
> delete all records except the one with the highest id for every datum
> whereas you only seem to delete the lowest id. There won't be any
> difference if there are two records on the same date, but with three
> records, the above sql deletes two of them - your original sql seems to
> only delete one.
>
> If you really wanted to only delete one in case of three duplicates (a
> very unusual requirement), use
>
> delete from zaras_futar_forgalmak z1
> where exists(
> select * from zaras_futar_forgalmak z2
> where z2.datum = z1.datum
> and z2.id > z1.id)
> and not exists(
> select * from zaras_futar_forgalmak z3
> where z3.datum = z1.datum
> and z3.id < z1.id)
>
> HTH,
> Set
>
> nagypapi wrote:
> > Maybe this is outdated:
> >
> > I am using fbserver1.5.2 on a winxp machine, and was browsing through
> > my database.
> >
> > The following sql statement worked perfectly in a few seconds, and
> > gave back 1 result:
> > select min(id) from zaras_futar_forgalmak where datum in
> > (select datum from zaras_futar_forgalmak group by datum
> > having count(distinct id)>1)
> >
> > but the following caused a 100% CPU load:
> > delete from zaras_futar_forgalmak where id in
> > (select min(id) from zaras_futar_forgalmak where datum in
> > (select datum from zaras_futar_forgalmak group by datum
> > having count(distinct id)>1)
> > )
> >
> >
> > The table ddl:
> > CREATE TABLE ZARAS_FUTAR_FORGALMAK (
> > ID BIGINT,
> > PERSONEL_ID BIGINT,
> > FUTAR_NEV VARCHAR(50),
> > CIMEK INTEGER,
> > FUTAR_FORGALOM BIGINT,
> > KUPON_DB INTEGER,
> > KUPON_FT BIGINT,
> > NEM_VETTEK_AT BIGINT,
> > SZAMLAK BIGINT,
> > ETKEZESI_JEGYEK BIGINT,
> > TULORA_CIMEK INTEGER,
> > FELAR INTEGER,
> > DATUM TIMESTAMP,
> > UTOLAG_ADOTT_KEDVEZMENY DOUBLE PRECISION
> > );
> >
> > The table has approximately 2000-5000 records
> >
> > I waited a few minutes, but it didn't finish, should I have waited

more?

> >
> > John
>