Hi all,

In one of our applications I have the following SP:

CREATE PROCEDURE PR_FETCHPIECES_A (
W_PRODUIT VARCHAR(50),
W_DATE_FICHE DATE,
W_HEUREDEBUT NUMERIC(15, 2)
) RETURNS (
DATE_FICHE DATE,
PIECES_A NUMERIC(15, 2),
HEUREDEBUT NUMERIC(15, 2)
) AS
declare variable lFirst char(1);
begin
lFirst = 'Y';
for select f.date_fiche, fc.pieces_a, fc.heuredebut
from fichescorps fc
join fiches f on f.code = fc.code
where fc.produit = :w_produit
and (f.date_fiche + 0 < :w_date_fiche or ((f.date_fiche + 0 =
:w_date_fiche) and (fc.heuredebut < :w_heuredebut)))
order by f.date_fiche DESC, fc.heuredebut DESC into :date_fiche,
:pieces_a, :heuredebut
do begin
if (lFirst = 'Y') then suspend;
else exit;
lFirst = 'N';
end
end

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.

Is this possible? Any ideas?

Regards,
Jonathan Neve.