Repeatable results when using ORDER BY

Say, we have a table with ID 开发者_高级运维column (unique values) and Code (non-unique). There are no insert/delete/update operations, i.e. it's kind of constant. We want it sorted by Code:

select ID, Code from T1 order by Code

So we get something like:

 4  'a'
 9  'a'
 1  'b'
 3  'b'

Does SQL standard or specific implementations (which ones, then) guarantee ID ordering in subsequent selects? I mean we don't get something like:

 9  'a'
 4  'a'
 1  'b'
 3  'b'

you can make it guarantee this way:

select ID, Code from T1 order by Code, ID

There is no specified default order in the SQL standard. This means your db engine is free to return the rows in whatever order it chooses (most likely the order in which the rows can be returned most efficiently).

Given this table structure the rows would most likely be returned in either insert order or ordered by the primary key, but it isn't anything you can depend on without specifying an ORDER BY clause.

The order is only guaranteed by the ORDER BY clause. You must add another column to the clause that will make the results repeatable.

If there are no changes to the DB, then you should see the same as they'll just be output in the order they exist in the database, and there's no reason for the DB to reorder records if there's no insert/updates. I don't believe there's any guarantee however, and you may be able to come up with situations where some of the records are in the memory cache and some aren't which could change the order, depending on the specific DB engine you're using.

Row order is only ever guaranteed when ORDER BY is specified.

Do you have an example where you need a guaranteed order, but for some reason are unable to use ORDER BY?

You can use your own ordering for both columns

select id, code from t1 order by code asc, id asc

Altough default is ascending anyway, however I have explicitly mentioned to show that you can specify order for both columns seperately as asc or desc