The forum will be unavailable for maintenance at some point from Friday, April 13 at 19:00 EDT until Sunday, April 15 at 23:59 EDT. Downtime will be minimized but the exact timing is unknown.

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

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
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
accept rate: 33%

retagged 18 Nov '12, 10:36

Nica%20_SAP's gravatar image

Nica _SAP

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.

permanent link

answered 07 Nov '11, 18:59

Siger%20Matt's gravatar image

Siger Matt
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
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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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: 07 Nov '11, 18:15

question was seen: 1,417 times

last updated: 18 Nov '12, 10:36