Re: Hey! ORDER BY in VIEWS?

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