2017-11-06 11:11 GMT+01:00 Cecil Westerhof <cldwester...@gmail.com>:

> 2017-11-06 10:39 GMT+01:00 Keith Medcalf <kmedc...@dessus.com>:
>
>>
>> The easiest way is likely to make the query so that it cannot be
>> flattened by adding an ORDER BY (that does not reference the column
>> containing the non-deterministic function by name -- ie, use only OUTPUT
>> column position indicators (ordinals), not names or aliases).  This will
>> require the query planner to use a co-routine for the inner table so that
>> only the values will get passed up to the outer query.
>>
>
​It looks like that is not necessary.​

​I played again a little with it. It could be optimised a bit by sorting on
the date, so it will be almost sorted correctly in the inner sort. I should
just use 3 then.

But I just tried what happens if I order by name and that seems to work OK
also.

I have now:
SELECT *
,      Randomiser
,      Randomiser
FROM (
    SELECT   *
    ,        abs(random()) / CAST(1.4E18 AS INTEGER) AS Randomiser
    FROM     teaInStock
    ORDER BY "Last Used"
)
ORDER BY randomiser + IFNULL(JULIANDAY("Last Used"), 0) ASC
LIMIT    5

And this gives:
"Goudsbloem"        "2017-10-22"    "3"     "2"    "2"    "2"
"Groene Sencha"     "2017-10-29"    "B6"    "0"    "0"    "0"
"Lemon"             "2017-10-24"    "B2"    "6"    "6"    "6"
"Darjeeling"        "2017-10-30"    "5"     "0"    "0"    "0"
"Ginger Lemon Chai" "2017-10-30"    "D4"    "1"    "1"    "1"
​

​So you can use the name. Something I prefer vastly above positional.
​


> Depending on the version of SQLite you are using, which you did not
>> mention.
>>
>> SELECT ....
>>   FROM (  SELECT ...
>>             FROM ...
>>         ORDER BY 1)
>> ORDER BY ...
>> LIMIT ...
>> ;
>>
>
> ​That works likes a charm. I now use:
>     SELECT "Last Used"
>     ,      Randomiser
>     ,      Randomiser
>     ,      Randomiser + IFNULL(JULIANDAY("Last Used"), 0)
>     FROM (
>         SELECT   *
>         ,        abs(random()) / CAST(1.4E18 AS INTEGER) AS Randomiser
>                  FROM     teaInStock
>         ORDER BY 1
>     )
>     ORDER BY randomiser + IFNULL(JULIANDAY("Last Used"), 0) ASC
>     LIMIT    5
>
> And this gives for example:
> ​
>
> ​"2017-10-17"    "2"    "2"    "2458045.5"
> "2017-10-20"    "0"    "0"    "2458046.5"
> "2017-10-19"    "3"    "3"    "2458048.5"
> "2017-10-18"    "6"    "6"    "2458050.5"
> "2017-10-19"    "5"    "5"    "2458050.5"​
>
> So problem solved. The double order is inefficient, but is only used
> during debugging, which now works. :-D
>
> Thanks.
>
>
> By the I am testing it in “DB Browser for SQLite” which uses 3.15.2.
>
> I am not sure in which programming language the real version will be
> implemented, but it will probably use a version near that one. For example
> my Java uses 3.20.0.
>

-- 
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to