CODEWITHSUNDEEP
javaandroidsqlite
Trey Balut
Trey Balut

Reputation: 1395

sqlite return a random record in a table

How do I return a random record in a SQLite table. For example, I have a table with 750 records, I want to return the 657th record. (any record will do). What would be the SQL syntax? I have an autoincrementing field as my Id primary key, but I don want to use the key field, since the id use is not recommended. I'll be searching a db in an Android App.

TIA

Upvotes: 0

Views: 412

Answers (2)

Diego Torres Milano
Diego Torres Milano

Reputation: 69198

You may try something like this that should be more efficient

select * from table
   where rowid =
      (select round(abs(random()/9223372036854775807.0)*max(rowid)+1) from table);

If you have deleted rows this select might return no rows, so if this is the case you may need to requery.

Upvotes: 0

paulsm4
paulsm4

Reputation: 121649

Try this:

SELECT * FROM table ORDER BY RANDOM() LIMIT 1;

Upvotes: 2

Related Questions