simple

put an index on Source_id desc, date desc (multi column index)

and do

SELECT FIRST 1 r.ID from RESULTS r
WHERE r.SOURCE_ID = :SourceId
ORDER BY Source_id desc, r.DATE DESC

if it's not work try to specify the plan in the select to use the good index

the result will be immediate

--- In firebird-support@yahoogroups.com, "korkless" <korkless@...> wrote:
>
> hi all, i have this table
>
> RESULTS
> {
> integer ID //PK
> integer SOURCE_ID //FK
> timestamp DATE
> }
>
> with an index on SOURCE_ID.
>
> i need a store procedure wich, given a source_id, will return the id of the row with the last DATE.
> i found 2 solutions:
>
> using 'order by' and selecting the first
>
> SELECT FIRST 1 r.ID from RESULTS r
> WHERE r.SOURCE_ID = :SourceId
> ORDER BY r.DATE DESC
>
> using the max function
>
> SELECT FIRST 1 r.ID FROM RESULTS r
> WHERE r.SOURCE_ID = :SourceId AND
> r.DATE = (SELECT MAX(DATE) FROM RESULTS r2
> WHERE SOURCE_ID = :SourceId)
>
> at the moment both are too slow (the table dimension is of same milions of records) so i'm trying to find same optimization.
> the solution 2 in my idea must be faster becouse it don't need to sort all the records but in my test it's about 20/30% slower, i'm doing/thinking samething wrong?
> and in general do you have same suggestion to do a better query to find what i need?
>
> thanks
>