Does order by in view guarantee order of select?

Question

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)?

Answer 1

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?

Answer 2

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.