Re: Built in Functions use with recordsets - Mailing list pgsql-general

"Gordan Bobic" <gordan@freeuk.com> writes:
> over a certain set of records. In effect, I want to do something like:

> SELECT somefield
> FROM sometable
> WHERE otherfield = 'criteria'
> ORDER BY thirdfield
> LIMIT 10

> and then do an avg(somefield).

> Can this be done without using temp tables, in a single query?

Not if the order by/limit are essential to selecting the rows you need
to average over.  At least not in 7.0 ... in 7.1 this'll work:

SELECT avg(somefield) FROM
(SELECT somefield FROM sometable ... LIMIT 10) t1;

For the moment a temp table is the way to go.

            regards, tom lane