SQLite Android Select Random Row Excluding Certain Columns

Question

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?

There is no first and last row, at least not just according to the data you posted. You need to tell us which _column_ determines which row is first and last. — Tim Biegeleisen, Oct 12 '18 at 02:14
Thanks Tim, I just need to know that if, for example, if row 2 is randomly chosen that selection F will be selected with selection H. Let assume that selection F is the label describing selection H so I need them to be together. It would just be one row that is selected at a time so I'm not concerned with the row order. I could have explained that more clearly in the question. I apologize. — Stark, Oct 12 '18 at 02:34
Unfortunately, you didn't answer my question. Which _column_ determines which row is "first" and which row is "last?" — Tim Biegeleisen, Oct 12 '18 at 02:35

score 1 · Accepted Answer · answered Oct 12 '18 at 02:16

1

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;

answered Oct 12 '18 at 02:16

Tim Biegeleisen

502,043
27
286
360

@TimBiegelisen I chose your answer because it seems extremely complete and I will try to implement it if needed. However, as I said above I ended up using column indexes for the listview but now I need to populate TextViews instead of the ListView. I made a new question for that. Thanks again. – Stark Oct 12 '18 at 21:25
https://stackoverflow.com/questions/52786983/android-display-multiple-pieces-of-cursor-data-together-textviews-instead-of-a – Stark Oct 12 '18 at 21:45

score 1 · Answer 2 · answered Oct 12 '18 at 02:17

1

you can use this query

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

answered Oct 12 '18 at 02:17

Fenrir

138
1
11

I ended up just calling the colum indexes and it is producing the desired result as far as populating the ListView. I just posted another question about populating textViews instead of the ListViews with the information. – Stark Oct 12 '18 at 21:22

score 1 · Answer 3 · answered Oct 12 '18 at 02:43

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()

i appreciate the suggestion. if it is more efficient I will try it. random() has been working for me so far. — Stark, Oct 12 '18 at 21:21

SQLite Android Select Random Row Excluding Certain Columns

3 Answers3