Eric Bohlman wrote:
Shum [Ming Yik] wrote:
Actually Sqlite same as other SQL in return order such as sample (1) ( it seems follow the insert order when no specific order by ... )
But Sqlite break the rule (may be it is not a rule), when part of the whole
Order by string passing into the select statement ... as sample (2)
Why not both in a same way ?
I meams: 1.) both in (descending order ) or 2.) both in ( ascending order)
I suggest you get hold of an algorithms textbook and read the section on sorting, paying particular attention to the distinction between "stable" and "unstable" sorting algorithms. You seem to be complaining that SQLite offers an unstable sort, but there's nothing in the SQL standards to require a stable sort.
As others have said, you're skating on thin ice if you make *any* assumptions about the ordering of rows coming out of a SELECT other than those explicitly stated in an ORDER BY clause. There's no reason any implementation should even be required to return rows in the same order on two successive identical SELECTs, and I can easily think of implementations that wouldn't do so (e.g. due to caching).
OK, there is no requirement, but there is a wish to return a stable sorting outcome. Please note that the SQL standard does not prohibid stable sorting outcome. Please also note that by adding the rowid an unstable sort-algorithm can produce a stable sort-outcome.
regards Bert Verhees
