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?

asked 07 Nov '11, 18:15

Derli%20Marcochi's gravatar image

Derli Marcochi
1.6k323877
accept rate: 33%

retagged 18 Nov '12, 10:36

Nica%20_SAP's gravatar image

Nica _SAP
866722


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

permanent link

answered 07 Nov '11, 18:59

Siger%20Matt's gravatar image

Siger Matt
3.2k496697
accept rate: 13%

edited 07 Nov '11, 19:00

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.

permanent link

answered 08 Nov '11, 02:58

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

Thanks to confirm that!

(08 Nov '11, 09:17) Derli Marcochi
Your answer
toggle preview

Follow this question

By Email:

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

By RSS:

Answers

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:

×61
×17
×13
×10

question asked: 07 Nov '11, 18:15

question was seen: 1,175 times

last updated: 18 Nov '12, 10:36