|
We came across an issue with a query without an ORDER BY clause that is returning records in a different order when comparing the results obtained in 5.5.05 and in 12.0.1.3484. Query 1: SELECT TranID, K.Description FROM BW.CKWriterTranList K, BW.TransactionDefs where TranID = ID 10003,'PO - #04 Passbook Inside Cover' 10000,'PO - #01 Charge/Credit Ticket' 10001,'PO - #02 Envelope' 10004,'PO - #05 CD Inside Cover' Query 2: SELECT K.TranID, T.Description FROM BW.CKWriterTranList K, BW.TransactionDefs T where K.TranID = T.ID TranID,Description 10000,'PO - #01 Charge/Credit Ticket' 10001,'PO - #02 Envelope' 10003,'PO - #04 Passbook Inside Cover' 10004,'PO - #05 CD Inside Cover' Is there any parameter/option that I should turn ON/OFF to ensure both queries would result records in the same order? |
|
I've seen something like this under different circumstances and asked about it. The answer is usually that the order of the records returned is not guaranteed, even from query to query within the same database, unless you use an ORDER BY. http://sqlanywhere-forum.sap.com/questions/1508/why-are-union-all-results-returned-in-this-order |
|
No, there's no such parameter, and I would think that there cannot be one: By definition the rows in a table of a RDBMS do not have an implicit order. AFAIK, without an ORDER BY, very old versions seem to tend to return result sets in the PK order (which you seem to expect as the "natural order") whereas newer versions take the freedom to return result sets in the most efficient way. So, as Siger has told, you will have to use an explicit ORDER BY clause. Thanks to confirm that!
(08 Nov '11, 09:17)
Derli Marcochi
|
