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

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.

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.

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

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of emb_blaster
Sent: 17. mars 2009 18:04
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Select for take first record from tableB for each record in tableA

Hi all,
I was searching for a way to achieve the same result
as aggregate function first(column). Example:

I had a Customer table, and a Sales table so I wish to
Select the Sales with Customers that din´t payed the bill :) and
the bill was 30 days old.
Now is the problem, I want only the oldest bill.

So, I´m doing this:

select C.name, SS.*, (current_date - SS.date) as DelayedDays
from customer C
inner join Sales SS on C.ID = SS.IDCustomer
where SS.PKSales = (select first 1 stemp.PKSales
from Sales stemp
where (stemp.PayDate is null) and (current_date - stemp.date > 30 ) and (stemp.IDCustomer = C.ID)
order by (stemp.date))
order by C.name

Using FB 2.0 there´s a way to simplify or optimize this query?
I don´t believe that I´m doing the rigth way this time,
... but still don´t know why ;)