Helen will say - this is not a support list... Go to firebird-support.

On Fri, 21 Jan 2005 09:45:45 +0100, Jonathan Neve wrote:

>
>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.
>
>
>
>Yahoo! Groups Links
>
>
>
>
>
>
>