On 17 August 2015 at 21:50, Simon Slavin <slavins at bigfraud.org> wrote: > > 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
So: SQLite version 3.8.11.1 2015-07-29 20:00:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> sqlite> create table myTable( id integer ); sqlite> insert into myTable values(1),(2),(3),(4),(5); sqlite> SELECT r FROM (SELECT random() AS r FROM myTable) ORDER BY r DESC; -6629212185178073901 -5293473521544706766 2649466971390864878 -6185422953036640443 1855956853707028764 sqlite> > Alternatively I think you could use WITH (CTE format) but I would like > someone more familiar with its syntax to figure it out. > > Simon. Regards, Simon
