Does order by in view guarantee order of select?

I have view for which it only makes sense to use a certain ordering. What I would like to do is to include the ORDER BY clause in the view, so that all SELECTs on that view can omit it开发者_运维技巧. However, I am concerned that the ordering may not necessarily carry over to the SELECT, because it didn't specify the order.

Does there exist a case where an ordering specified by a view would not be reflected in the results of a select on that view (other than an order by clause in the view)?

You can't count on the order of rows in any query that doesn't have an explicit ORDER BY clause. If you query an ordered view, but you don't include an ORDER BY clause, be pleasantly surprised if they're in the right order, and don't expect it to happen again.

That's because the query optimizer is free to access rows in different ways depending on the query, table statistics, row counts, indexes, and so on. If it knows your query doesn't have an ORDER BY clause, it's free to ignore row order in order (cough) to return rows more quickly.

Slightly off-topic . . .

Sort order isn't necessarily identical across platforms even for well-known collations. I understand that sorting UTF-8 on Mac OS X is particularly odd. (PostgreSQL developers call it broken.) PostgreSQL relies on strcoll(), which I understand relies on the OS locales.

It's not clear to me how PostgreSQL 9.1 will handle this. In 9.1, you can have multiple indexes, each with a different collation. An ORDER BY that doesn't specify a collation will usually use the collation of the underlying base table's columns, but what will the optimizer do with an index that specifies a different collation than an unindexed column in the base table?

Couldn't see how to reply further up. Just adding my reply here.

You can rely on the ordering in every case where you could rely on it if you manually wrote the query.

That's because PostgreSQL rewrites your query merging in the view.

CREATE VIEW v AS SELECT * FROM people ORDER BY surname;
-- next two are identical
SELECT * FROM v WHERE forename='Fred';
SELECT * FROM people WHERE forename='Fred' ORDER BY surname;

However, if you use the view as a sub-query then the sorting might not remain, just as the output order from a sub-query is never maintained.

So - am I saying to rely on this? No, probably better all round to specify your desired sort order in the application. You'll need to do it for every other query anyway. If it's a utility view for DBA use, that's a different matter though - I have plenty of utility views that provide sorted output.

While observations have so far been true for the following, this answer is not definitive by any means. @Catcall and I, both, could not find anything definitive in the documentation and I have to admit, I'm too lazy to wade through and make sense of the source code.

But for observations sake, consider the following:

1. SELECT * FROM (select * from foo order by bar) foobar;
   

   The query should return ordered.

2. SELECT * FROM vw_foo; -- where vw_foo is the sub-select above
   

   The query should return ordered.

3. SELECT * FROM vw_foo LEFT JOIN (select * from bar) bar ON vw_foo.id = bar.id;
   

   The query should use it's own discretion and may return unordered.

Disclaimer:

Much like @Catcall said, you should never truly depend on any implicit sorting, as many times it will be left up to the database engine. Databases are designed for quickness and reliability; they often interface with memory and try to pull/push data as quickly as possible. However, the ordering isn't solely based on memory management, there are several factors that are involved.

Unless you have something specific in mind, you should do your sorting at the end (on the outer query).

If the above observation was true, something like the following should always turn the results in the correct order:

SELECT * 
FORM   (select   trunc(random()*999999+1) as i 
        from     generate_series(1,1000000) 
        order by i
       ) foo;

The simple process would be: perform preprocessing and perform query identification (identify that an order exists), start loop, fetch first field (generate random number), add to output stack in sorted order. The ordering may also occur at the end of the stack generation, instead of during (eg compile the list and then do the sorting). This depends on versioning and the query.