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

shaner's gravatar image

accept rate: 0%


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.

(12 Jul '11, 08:43) Mark Culp

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?

(12 Jul '11, 10:34) shaner
Replies hidden

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...

(13 Jul '11, 03:32) Volker Barth

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
permanent link

answered 12 Jul '11, 10:23

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

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.


permanent link

answered 12 Jul '11, 06:00

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
accept rate: 15%

edited 12 Jul '11, 06:01

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 11 Jul '11, 17:18

question was seen: 932 times

last updated: 13 Jul '11, 03:32