On 15 Jan 2013, at 1:58pm, Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote:
> On 14/01/2013 9:48 AM, François-xavier Jacobs wrote:
>> i would like to "seed random" a request, so i could do use some pagination
>> system with a order by random(), is this possible sqlite ? when a tried to
>> use "order by 1234567892" it always return the same order
sqlite> CREATE TABLE myTable (ax TEXT, myOrder INTEGER);
sqlite> INSERT INTO myTable (ax) VALUES ('fred');
sqlite> INSERT INTO myTable (ax) VALUES ('wilma');
sqlite> INSERT INTO myTable (ax) VALUES ('barney');
sqlite> INSERT INTO myTable (ax) VALUES ('betty');
sqlite> INSERT INTO myTable (ax) VALUES ('pebbles');
sqlite> INSERT INTO myTable (ax) VALUES ('bamm bamm');
sqlite> SELECT * FROM myTable;
fred|
wilma|
barney|
betty|
pebbles|
bamm bamm|
sqlite> SELECT * FROM myTable ORDER BY random();
betty|
wilma|
bamm bamm|
fred|
barney|
pebbles|
sqlite> SELECT * FROM myTable ORDER BY random();
betty|
pebbles|
fred|
wilma|
barney|
bamm bamm|
> That's because you told sqlite that the distinguishing feature of a tuple is
> the constant '1234567892' . The sorting routine will see "t1 < t2 = false"
> and "t1 > t2 = false" (implying t1 = t2) no matter which two tuples it
> examines. If sqlite uses a stable sorting algorithm, that will leave the
> input untouched. A very expensive no-op, in other words.
>
> With random(), t1 < t2 is undefined (because it turns out different every
> time it's evaluated), and you end up with tuples in a random order (assuming
> the sorting routine doesn't crash; code usually assumes values don't change
> while being sorted). Note that shuffling directly is more efficient than
> "sorting" in this way, though sqlite lacks the necessary "shuffle" operator
> to do it in-database.
If you're going to search and seek you actually have to have the random values
stored, because you need to get consistent results when you look for/at a
value. One way to get rows in a random order is to put an extra column in your
table, with an INTEGER affinity and random values. When you want to shuffle
the order just update the values in the table. Then you can do an "ORDER BY
myOrder" and everything comes out consistently:
sqlite> UPDATE myTable SET myOrder=random();
sqlite> SELECT * FROM myTable ORDER BY myOrder;
barney|-8843778138112958223
bamm bamm|-7611867930025301398
pebbles|-5851529134925141201
wilma|-2528762072213537603
betty|853939383612226875
fred|8581781187410411890
There's no problem with ordering by these 'huge numbers'.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
