Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id E0E442E00F4 for ; Thu, 28 Feb 2008 14:09:26 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 73224-02 for ; Thu, 28 Feb 2008 14:09:21 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail.mailpen.net (mail.mailpen.net [209.90.228.14]) by postgresql.org (Postfix) with ESMTP id C6ABF2E00F0 for ; Thu, 28 Feb 2008 14:09:20 -0400 (AST) Received: from [172.24.2.1] (pool-71-113-24-134.sttlwa.dsl-w.verizon.net [71.113.24.134]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.mailpen.net (Postfix) with ESMTP id 67F643EE084 for ; Thu, 28 Feb 2008 10:09:19 -0800 (PST) Message-ID: <47C6F8EE.1080409@ultimeth.com> Date: Thu, 28 Feb 2008 10:09:50 -0800 From: "Dean Gibson (DB Administrator)" Reply-To: pgsql-performance@postgresql.org Organization: UltiMeth Systems User-Agent: Thunderbird 2.0.0.12 (Windows/20080213) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Bypassing useless ORDER BY in a VIEW References: <36af4bed0802280631ja29c636w2256fb329e14b95e@mail.gmail.com> <8366.1204218820@sss.pgh.pa.us> In-Reply-To: <8366.1204218820@sss.pgh.pa.us> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/395 X-Sequence-Number: 28937 On 2008-02-28 09:13, Tom Lane wrote: > A rule of thumb is that ORDER BY in a view is bad design, IMHO. > > regards, tom lane > I was surprised to find out that apparently it's also a PostgreSQL extension; standard SQL apparently disallows ORDER BY in VIEWs: http://en.wikipedia.org/wiki/Order_by_(SQL) When I found this out, I removed all the ORDER BYs from my VIEWs (which had been there for the convenience of subsequent SELECTs). Of course, where ORDER BY in a VIEW is really helpful, is with OFFSET and/or LIMIT clauses (which are also PostgreSQL extensions), which is equivalent to what you point out. -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce.