[sqlite] Need two ORDER two times when using RANDOM

[Cecil Westerhof]

I have the following query:
SELECT *
,      randomiser
,      randomiser
FROM   (
    SELECT *
    ,      ABS(RANDOM()) / 5E17 AS randomiser
    FROM   proverbs
    WHERE  used <> 'notUsed'
    ORDER BY randomiser + IFNULL(JULIANDAY(used), 0) ASC
)
ORDER BY randomiser + IFNULL(JULIANDAY(used), 0) ASC

And this gives for example:
"Voor niets gaat de zon op."    "2017-01-12"    "0.337325790117148"
"0.337325790117148"    "0.337325790117148"
"Met de wolven in het bos meehuilen."    "2017-01-11"
"2.59601454335206"    "2.59601454335206"    "2.59601454335206"
"Als katten muizen,
mauwen ze niet."    "2017-01-11"    "2.7932230420896"
"2.7932230420896"    "2.7932230420896"
"Uitstel is afstel."    "2017-01-12"    "1.88933779146209"
"1.88933779146209"    "1.88933779146209"
"Het is rozengeur en maneschijn."    "2017-01-13"    "1.16363975452034"
"1.16363975452034"    "1.16363975452034"

But when I remove the last ORDER (which seems redundant) I get:
"Het is rozengeur en maneschijn."    "2017-01-13"    "3.0795495790489"
"2.7886449148631"    "6.78717082169993"
"Met de kippen op stok."    "2017-01-16"    "6.56483737827297"
"1.32007069440753"    "5.18920985400017"
"Schoenmaker,
blijf bij je leest."    "2017-01-12"    "8.17180081902947"
"5.91518750003302"    "4.78956808218011"
"Een goed begin is het halve werk."    "2017-01-17"
"7.70627730482033"    "15.580638189131"    "2.0146022387495"
"Zoals het klokje thuis tikt,
tikt het nergens."    "2017-01-18"    "8.53059705262686"
"15.7307229942"    "1.1516802288132"

So randomiser is not stable any-more.


For the moment I work with:
SELECT   *
,        randomiser
,        randomiser
FROM   (
    SELECT   *
    ,        ABS(RANDOM()) / 5E17 AS randomiser
    FROM     proverbs
    WHERE    used <> 'notUsed'
    ORDER BY used ASC
)
ORDER BY randomiser + IFNULL(JULIANDAY(used), 0) ASC

​Then randomiser is stable and the time needed is only slightly more as
time for the second query. (It is about 43, 53 and 46 ms.)​

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