Jonathan Neve wrote:

>
> What I'm trying to acheive is simply this: I want to select the most
> recent record in FICHESCORPS where PRODUIT has a certain specified
> value, based on a date field in a linked table called FICHES. The
> thing is, I find this type of query very awkward and clumsy, and not
> always easy to optimize... Is there (or could there be) a better way
> to this? After all, what I really want is simply a MAX on the
> DATE_FICHE field of the FICHES table. But of course, the problem is
> that MAX is an aggregate function, so this isn't possible. What would
> be good, would be if it were possible to find the record that has the
> max or min value of a certain field, but then also have access to the
> other fields of that record... In other words, have a way of
> selecting the record with the MIN / MAX values of a given field,
> rather than merely selecting the MIN / MAX value itself.

I don't know why Arno didn't chime in, but he implemented select from select
and it works, although I can't comment on the performance you would get.
Example follows:

F:\fb2dev\fbbuild\firebird2\temp\debug\firebird\bin>isql TRACK_PLAN.FDB
Database: TRACK_PLAN.FDB
SQL> select max(rdb$relation_name) from rdb$relations;

MAX
===============================

TTT

SQL> select first 1 rdb$relation_name, rdb$relation_id from rdb$relations
order by 1 desc;

RDB$RELATION_NAME RDB$RELATION_ID
=============================== ===============

TTT 129

SQL> select first 1 * from (select rdb$relation_name, rdb$relation_id from
rdb$relations order by 1 desc);

RDB$RELATION_NAME RDB$RELATION_ID
=============================== ===============

TTT 129

SQL> ^Z

Really, the second case can give you what you want, but if you have GROUP BY
or subselects, you will need the third form.

C.