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?
asked 11 Jul '11, 17:18
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:
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
answered 12 Jul '11, 10:23
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.