2017-11-06 11:11 GMT+01:00 Cecil Westerhof <[email protected]>:
> 2017-11-06 10:39 GMT+01:00 Keith Medcalf <[email protected]>:
>
>>
>> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
