Received: from finch-post-10.mail.demon.net (finch-post-10.mail.demon.net [194.217.242.38]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6G7vta67476 for ; Mon, 16 Jul 2001 03:57:56 -0400 (EDT) (envelope-from dev@archonet.com) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by finch-post-10.mail.demon.net with esmtp (Exim 2.12 #1) id 15M2PZ-0009tj-0A; Mon, 16 Jul 2001 07:03:22 +0000 Received: from archonet.com (ibook.archonet.com [192.168.1.32]) by mainbox.archonet.com (Postfix) with ESMTP id 822B41427E; Mon, 16 Jul 2001 07:40:21 +0100 (BST) Message-ID: <3B528BCF.92DEC4BB@archonet.com> Date: Mon, 16 Jul 2001 07:38:08 +0100 From: Richard Huxton X-Mailer: Mozilla 4.7C-CCK-MCD {C-UDP; EBM-APPLE} (Macintosh; I; PPC) X-Accept-Language: en MIME-Version: 1.0 To: Josh Berkus Cc: Peter Eisentraut , pgsql-sql@postgresql.org Subject: Re: Hey! ORDER BY in VIEWS? References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200107/196 X-Sequence-Number: 3991 Josh Berkus wrote: > > Pater, Robbie, Bruce, > > Makes sense. I take it that this is a deviation from the ANSI 92 > standard, then? > > What happens if I put an ORDER BY in a view, then call an ORDER BY in a > query, e.g.: > Does the second ORDER BY override or suppliment the view ORDER BY, or is > it ignored? It overrides. People seem to be forgetting ORDER BY ... LIMIT has selective qualities as well as ordering ones. The example someone gave me was when you use LIMIT ... OFFSET to fetch results a page at a time. If you want the last page of your results you need to do something like: SELECT * FROM messages ORDER BY msg_timestamp DESC LIMIT 20; But - this gives them in reverse timestamp order. So - wrap the query in a view and then apply your own ORDER BY. Can't remember who came up with this (some evil genius :-) - but it seemed to make sense so I stuck the example in my PostgreSQL notes. - Richard Huxton