"Gordan Bobic" <gordan(at)freeuk(dot)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