Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A5F89476911 for ; Mon, 6 Jan 2003 11:34:51 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id BBD7B4769E7 for ; Mon, 6 Jan 2003 11:34:36 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h06GYZ0U014735; Mon, 6 Jan 2003 11:34:35 -0500 (EST) To: Tomasz Myrta Cc: pgsql-sql@postgresql.org Subject: Re: order by and aggregate In-reply-to: <3E197A39.8070808@klaster.net> References: <3E197A39.8070808@klaster.net> Comments: In-reply-to Tomasz Myrta message dated "Mon, 06 Jan 2003 13:44:41 +0100" Date: Mon, 06 Jan 2003 11:34:35 -0500 Message-ID: <14734.1041870875@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/40 X-Sequence-Number: 10905 Tomasz Myrta writes: > I found I can obey this using subselect: > select > maxsum(X.value) > from > (select value > from some_table > order by some_field) X > I can't create subselect, because I want to change this query into a > view. In my case postgresql doesn't use indexes on subselect inside a view. I don't understand what you think is wrong with this solution. It works fine for me: regression=# create view vv as regression-# select max(unique1) from (select unique1 from tenk1 regression(# order by unique2) x; CREATE VIEW regression=# explain select * from vv; QUERY PLAN --------------------------------------------------------------------------------------------------- Subquery Scan vv (cost=523.00..523.00 rows=1 width=8) -> Aggregate (cost=523.00..523.00 rows=1 width=8) -> Subquery Scan x (cost=0.00..498.00 rows=10000 width=8) -> Index Scan using tenk1_unique2 on tenk1 (cost=0.00..498.00 rows=10000 width=8) (4 rows) regards, tom lane