SQLite Random() In ORDER BY not sorting correctly

Question

My SQLite query seems to be returning results out of the prescribed "ORDER BY" clause. When I run the following, the third column IS giving me the random values I want, but the ORDER BY looks like it is being ignored. The result set is seemingly randomly ordered.

// frequency is an int of either 5, 10, or 15
String query = "SELECT id, name, ABS( RANDOM() % frequency ) FROM person ORDER BY 3"

SQLiteDatabase database = this.getReadableDatabase();
Cursor cursor = database.rawQuery(query, null);

Returns something like:

id    name    (random)
------------------------------
16    Joe     1
 4    Jack    0
67    Jane    7
23    Bob     3

I've tried many variations, including the following, but all with the similar results: rows not ordered on the third column:

"SELECT id, name, (RANDOM() % frequency) AS myRand FROM person ORDER BY myRand"
id    name    myRand
------------------------------
23    Bob     2
16    Joe     1
67    Jane    -4
 4    Jack    0

or

"SELECT id, name, CAST(ABS( RANDOM() % frequency ) AS TEXT) AS myRand FROM person ORDER BY myRand"

id    name    myRand
------------------------------
23    Bob     "1"
16    Joe     "2"
67    Jane    "1"
 4    Jack    "5"

in desperation, I even tried this...

"SELECT id, name, 'A' || CAST(ABS( RANDOM() % frequency ) AS TEXT) AS myRand FROM person ORDER BY myRand"
id    name    myRand
------------------------------
 4    Jack    "A9"
23    Bob     "A3"
67    Jane    "A8"
16    Joe     "A2"

The actual result values for the third column are, of course, different for each run, but the lack of sort on the third column is consistent. Why do the results not sort by the third column?

Answer 1

Why do the results not sort by the third column?

Because the column is an expression that is re-evaluated for ORDER BY purposes, producing different random values.

Sorry, at the moment can't think of a practical way to preserve the random order value on each row. Consider changing your requirements - ORDER BY RANDOM() does not perform well on large datasets anyway.