Repeatable results when using ORDER BY

Question

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'

Answer 1

you can make it guarantee this way:

select ID, Code from T1 order by Code, ID

Answer 2

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.