pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: Peter Eisentraut <peter_e@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Hey! ORDER BY in VIEWS?
Date: Mon, 16 Jul 2001 07:38:08 +0100
Message-ID: <3B528BCF.92DEC4BB@archonet.com> (raw)
References: <web-85953@davinci.ethosmedia.com>

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



view thread (11+ messages)  latest in thread

Message-ID: <3B528BCF.92DEC4BB@archonet.com>
Permalink:  ../3B528BCF.92DEC4BB@archonet.com/
Also on:    postgresql.org/message-id/3B528BCF.92DEC4BB@archonet.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: dev@archonet.com, josh@agliodbs.com, peter_e@gmx.net
  Subject: Re: Hey! ORDER BY in VIEWS?
  In-Reply-To: <3B528BCF.92DEC4BB@archonet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox