DB views are not sorting

[Former Member]

We just upgraded from SQL Anywhere 9.0.2 to ver 11.0.1 and all the views in our database are not sorting. It is ignoring the Order by clause. Is there some database setting that I missed which is causing the dbms to ignor the sorting built into the view's SQL statement?

[MarkCulp]

Please give us more detail. What do you mean by "not sorting"? Do you have a ORDER BY clause in your view and then you are doing a "select * from view"? If this is the case then you are under the misconception that you will get an ordered result because AFAIK SQL only guarantees the order if you put the ORDER BY in the outer most SELECT.

[Former Member]

Over the last year we have built several hundred views in SA 9 for a front-end SAS reporting tool. They all have ORDER BY clauses to have the data come out sorted into the SAS report. This has been working great. Now that we upgraded to SA 11.0.1 the ORDER BY clauses in all our views seem to be ignored. This functionality is much more important to us than any performance issues since we have nice size servers and small datasets. Is there any database option (or some method) to get the sorting to happen?

[VolkerBarth]

AFAIK, the query optimizer was completely rewritten for SA 10. As a consequence, the optimizer makes more use of its freedom to return result sets in the most performant way if the user has not specified an order - i.e. for queries lacking an outer ORDER BY clause. Therefore those queries return result sets in a more "randomized" order than with previous engines. That's by design and complies to the SQL standard.

E.g., when querying base tables, previous versions were rather likely to return result sets in primary key order when no ORDER BY was given, and that might give the impression the result sets were "automatically ordered". However, that would be a "right behaviour" by accident.

I guess you will have to enhance the reports to use an ORDER BY. Hopefully there's a way to do this somewhat programmatically as the ORDER BY could by taken from the view definition itself...

[VolkerBarth]

To add to Thomas's answer:

The 12.0.1 docs point out the following to the usage of ORDER BY in a view definition:

The semantics of queries dictates that the order of the rows returned is undefined unless the query combines an ORDER BY clause with a TOP or FIRST clause in the SELECT statement.
If an ORDER BY clause is specified along with either a TOP or FIRST clause, then the query returns the top n rows, or the first row, respectively.

I.e. using ORDER BY is quite useless (and according to Thomas, possibly bad w.r.t. performance) unless you want to use a TOP n / FIRST / LIMIT clause. To get an ordered result set, you will have to use

select * from myView order by myColumnList

[thomas_duemesnil]

The CREATE VIEW Help is clear about this

..., using a SELECT with an ORDER BY clause does affect the results of a view definition.

I have seen in SA 9 that I could create a View with order by but it was always a performance killer.

HTH