>
> I would normally do
>
> select C.name, SS.*, (current_date - SS.date) as DelayedDays
> from customer C
> inner join Sales SS on C.ID = SS.IDCustomer
> where SS.Date < current_date - 30
> and ss.PayDate is null
> and not exists(select null
> from Sales stemp
> where stemp.IDCustomer = C.ID
> and stemp.PayDate is null
> and (stemp.date < ss.Date
> or (stemp.date = ss.Date
> and stemp.rdb$db_key < ss.rdb$db_key)))
> and C.name not containing 'Tysvær' // ;o)
> order by C.name
>

Wow, I liked this part "and C.name not containing 'Tysvær'"
maybe I will include this in all my projects from now on ;)

> The NOT EXISTS part simply ascertains that only the record with earliest unpaid date gets through.
>
> If customers never buy twice within the same day, then you do not need the OR bit. I normally use the primary key field rather than rdb$db_key, but I didn't know whether you have a primary key field and using rdb$db_key should be at least as quick. If your primary key is autogenerated and all sales occur chronologically, you could use this PK rather than 'DATE' for stemp, possibly making it easier for people to read the code.
>

well this is not the case... :[

> I assume this to be quicker than your SQL (you may want to add +0 to stemp.date and/or stemp.PayDate for optimization, that depends on various fields selectivity), but I have never tested your type of sql.
>

yes it need more tests. We are tryng this SQL in two FDB. one is mine with fews thousands of records in FB 2.0, and another one from another guy with FB 2.1 with a huge table (16000 in costumers and 400000 in sales). In my DB there´s no index in any date field and your query and mine are instantly.
but in the other DB there's a index in stemp.date that probably is slowing process in sort time (the index is not used in plan to select data, only in sort). your query is slowest more than one minute, but not tested without index yet (+0 in date field). So it can be fastest. After test I'll send another post, ok?

> I fairly often execute this kind of query (well, not for sales) on Firebird 1.5 and believe it to work equally well on all versions.
>
> HTH,
> Set

Thank you again for help us,
and sorry by the delay in reply.
Regards,
EMB