An alternative to Dimitrys suggestion is

select r.id, r.source_id
from results r
where not exists(select * from results r1
where r.source_id = r1.source_id
and r.date < r1.date)

The two alternative solutions should have equal result (and I'd expect them to execute about equally quickly), the slight theoretical difference is that Dimitry selects the last date for each source_id and then displays that record, whereas I display all records that doesn't have a later date for that particular source_id.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of korkless
Sent: 5. mars 2010 11:41
To: firebird-support@yahoogroups.com
Subject: [firebird-support] select multiple max

hi all, i have this table

RESULTS
{
integer ID //PK
integer SOURCE_ID //FK
timestamp DATE
}

i need to select this set

[SOURCE_ID, ID]

where ID is the id of the row witht row.SOURCE_ID = SOURCE_ID
and wich have the last timestamp.

(ie i need to do this query
SELECT FIRST 1 r.ID from RESULTS r
WHERE r.SOURCE_ID = :SourceId
ORDER BY r.DATE DESC
for each distict SOURCE_ID)

what's the best way to do that? thanks