In the case: SELECT random() AS rr FROM sometable ORDER BY rr, the SQLite result is anti-intuitive. In my ignorance, I thought that ORDER BY sorted the results of the SELECT. It sure _looks_ that way from my view point. I cannot access the ANSI standard because I'm too cheap to buy them. So I went to Wikipedia: https://en.wikipedia.org/wiki/Order_by (not authoritative, I know) and got the following: <quote> An *ORDER BY* clause in SQL <https://en.wikipedia.org/wiki/SQL> specifies that a SQL SELECT <https://en.wikipedia.org/wiki/Select_(SQL)> statement returns a result set <https://en.wikipedia.org/wiki/Result_set> with the rows being sorted by the values of one or more columns. The sort criteria do not have to be included in the result set. The sort criteria can be expressions, including ? but not limited to ? column names, user-defined functions <https://en.wikipedia.org/wiki/User_defined_function>, arithmetic operations, or CASE expressions. The expressions are evaluated and the results are used for the sorting, i.e. the values stored in the column or the results of the function call. <quote> So, applying this to our subject, "rr" is _NOT_ the name a column in the table. It is an alias to a "user-defined function" (user-defined in this case meaning not defined in the SQL standard itself). Therefore, based on the last sentence of the above quote, the random() is evaluated AT THE TIME THE ORDER BY IS EXECUTED. Therefore, although not what we might want and expect, SQLite is conformant. I think that this logic also applies in the case of a VIEW or CTE. Which basically means that, to get what is wanted, we need to first put our data into another, perhaps TEMPORARY, table. And then do the SELECT ... ORDER BY using that table with column names, not functions.
This is only my thought on the situation. I don't claim that it is correct. But it does, sort of [pun intended], explain the ORDER BY results which were observed. -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
