CODEWITHSUNDEEP
androidsqliterandom
Stark
Stark

Reputation: 107

SQLite Android Select Random Row Excluding Certain Columns

Is it possible to select a random row from an SQLite table however have the row return only specific column information for that row using Android rawquery sort by random? Example:

Table1

  C1      Col2   Col3    Col4

1 A       B      C       D

2 E       F      G       H

3 I       J      K       L

4 M       N      O       P

5 Q       R      S       T

6 U       V      W       X

Is it possible to randomly select a row between 1 and 6 excluding columns C1 and C3 thus returning:

J,L or N,P or V,X etc...

Any examples of a raw query SELECT statement that would accomplish this?

Upvotes: 1

Views: 194

Answers (3)

kukroid
kukroid

Reputation: 420

Use of Random inside a Db query is not recommended, as it could be quite expensive operation , if your data is too big . If it is 100 rows, then probably you can do whatever you want .

Random query traverses the whole db to generate Random row

Instead of that, you can do it in 2 steps 1) Find the count of rows 2) calculate a random number using Math.random from 1 to count-1 ; Then use the query as :

SELECT column FROM tableName LIMIT $random_number, 1

The process in both is same, but getting the random number inside sql query is expensive then getting it using Math.random()

Upvotes: 1

Fenrir
Fenrir

Reputation: 138

you can use this query

SELECT Col2, Col4 FROM yourTable ORDER BY RANDOM() LIMIT 1;

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

Here is one option, which assumes that the C1 column determines the ordering:

SELECT Col2, Col4
FROM yourTable
WHERE C1 <> (SELECT MIN(C1) FROM yourTable) AND
      C1 <> (SELECT MAX(C1) FROM yourTable)
ORDER BY RANDOM()
LIMIT 1;

Upvotes: 1

Related Questions