[sqlite] order by not working in combination with random()

[Simon Slavin]

On 17 Aug 2015, at 9:46pm, Jeffrey Mattox <jmat at mac.com> wrote:

> Could the random() be made part of an expression (that doesn't change the 
> result) to fool the optimizer into only doing the random() once, like this:
> 
> SELECT ( random() * col_thats_always_one ) AS x  FROM table  ORDER BY x

Use a sub-select:

SELECT r FROM (SELECT random() AS r FROM myTable) ORDER BY r DESC LIMIT 20

Alternatively I think you could use WITH (CTE format) but I would like someone 
more familiar with its syntax to figure it out.

Simon.