Subject | RE: [firebird-support] last function in aggregate query |
---|---|
Author | Kevin Day Programming |
Post date | 2004-05-17T04:00:01Z |
No but there is a FIRST (version 1.5) function which could give you the same
result as last in certain circumstances.
The first function allows you to return only the first n rows of the results
of a given select query.
Let others correct me if I'm incorrect or if my syntax is off.
To give you exactly what you want by a stored procedure you would:
CREATE PROCEDURE TOTAL_LAST_CLIENTS
RETURNS (ANNO INTEGER /*OR WHATEVER TYPE YOU ARE USING*/),
CLIENT INTEGER /OR *WHATEVER YOU TYPE YOU ARE USING*/,
TOTAL DECIMAL(18,6))
AS
BEGIN
FOR SELECT DISTINCT ANNO FROM PAYMENT ORDER BY ANNO INTO :ANNO DO
BEGIN
SELECT FIRST 1 CLIENT FROM PAYMENT
WHERE (ANNO = :ANNO)
ORDER BY CLIENT DESC
INTO :CLIENT
SELECT SUM(IMPORT) FROM PAYMENT
WHERE (ANNO = :ANNO) AND (CLIENT = :CLIENT)
INTO :TOTAL
SUSPEND
END
END
I'm sure it would be possible to do the same thing outside of a stored
procedure using nested sql if you have the time to work it out.
IMO, It will be somewhat more complicated than the stored procedure.
regards,
Kevin Day.
result as last in certain circumstances.
The first function allows you to return only the first n rows of the results
of a given select query.
Let others correct me if I'm incorrect or if my syntax is off.
To give you exactly what you want by a stored procedure you would:
CREATE PROCEDURE TOTAL_LAST_CLIENTS
RETURNS (ANNO INTEGER /*OR WHATEVER TYPE YOU ARE USING*/),
CLIENT INTEGER /OR *WHATEVER YOU TYPE YOU ARE USING*/,
TOTAL DECIMAL(18,6))
AS
BEGIN
FOR SELECT DISTINCT ANNO FROM PAYMENT ORDER BY ANNO INTO :ANNO DO
BEGIN
SELECT FIRST 1 CLIENT FROM PAYMENT
WHERE (ANNO = :ANNO)
ORDER BY CLIENT DESC
INTO :CLIENT
SELECT SUM(IMPORT) FROM PAYMENT
WHERE (ANNO = :ANNO) AND (CLIENT = :CLIENT)
INTO :TOTAL
SUSPEND
END
END
I'm sure it would be possible to do the same thing outside of a stored
procedure using nested sql if you have the time to work it out.
IMO, It will be somewhat more complicated than the stored procedure.
regards,
Kevin Day.