Thread: Aggregate functions

Hi.

I have a simple table in which I store equity prices:

table prices (
equity_id    integer,
date            date,
price          money);

I am happy with the following select statement:

SELECT equity_id, min(price), max(price)
FROM prices
WHERE date >= start_date AND date <= end_date
GROUP BY equity_id;

which produces a nice table of min and max prices for each equity between
two dates.

What I would like to be able to do is a select like this:

SELECT equity_id, start_date(price), end_date(price), min(price),
max(price)
...

Are there aggregate function, such as 'first' and 'last', that will store
the first and last row value of  a series of rows ?

I have read the Extending Aggregates section of the Programmers Guide but it
is a bit beyond me. Any assistance in writing functions to do the above
would be appreciated.

Regards
John Duffy
jbduffy@cwcom.net

"John Duffy" <jbduffy@cwcom.net> writes:
> What I would like to be able to do is a select like this:
> SELECT equity_id, start_date(price), end_date(price), min(price),
> max(price)
> Are there aggregate function, such as 'first' and 'last', that will store
> the first and last row value of  a series of rows ?

Don't min(date) and max(date) do what you want?  If not, please explain
what you want more carefully.

"First" and "last" aggregates would be trivial to write, but I doubt
they'd be useful for anything, because the rows of the table are not
guaranteed to be delivered to the aggregate function in any particular
order.  In general SQL does not believe that row ordering is a
meaningful concept --- ORDER BY is a decoration to produce nice-looking
output for human consumption, not part of the computational model.  So
you need to define the results you want without reference to row order.

            regards, tom lane