Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 1A24E2E00C4 for ; Thu, 28 Feb 2008 17:52:31 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 24085-01 for ; Thu, 28 Feb 2008 17:52:03 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 076DF2E015C for ; Thu, 28 Feb 2008 17:52:16 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m1SLqFQa016852 for ; Thu, 28 Feb 2008 16:52:15 -0500 (EST) To: pgsql-performance@postgresql.org Subject: Re: Bypassing useless ORDER BY in a VIEW In-reply-to: <47C6F8EE.1080409@ultimeth.com> References: <36af4bed0802280631ja29c636w2256fb329e14b95e@mail.gmail.com> <8366.1204218820@sss.pgh.pa.us> <47C6F8EE.1080409@ultimeth.com> Comments: In-reply-to "Dean Gibson (DB Administrator)" message dated "Thu, 28 Feb 2008 10:09:50 -0800" Date: Thu, 28 Feb 2008 16:52:15 -0500 Message-ID: <16851.1204235535@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/396 X-Sequence-Number: 28938 "Dean Gibson (DB Administrator)" writes: > 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. Right, which is the main reason why we allow it. I think that these are sort of poor man's cases of things that SQL2003 covers with "windowing functions". The SQL spec treats ORDER BY as a cosmetic thing that you can slap onto the final output of a SELECT. They don't consider it useful in subqueries (including views) because row ordering is never supposed to be a semantically significant aspect of a set of rows. regards, tom lane