lundi 27 août 2018

SQLITE random value/row with criteria

So, i know that the question about getting a random row in SQL database have been posted (even more than one time), and it helped me get to this SQL query:

"SELECT column FROM table WHERE id IN (SELECT id FROM table ORDER BY RANDOM() LIMIT 1)"; 

if i understand that well, order by Random is not efficient on large table but if you use it with the id it's fast enough.

So i've tried it, it seems to work well, but then i wanted to add a criteria, so i've changed it just a little bit:

"SELECT column1 FROM table WHERE (column2 LIKE '" + value + "' AND id IN (SELECT id FROM table ORDER BY RANDOM() LIMIT 1)"; 

To be more clear, i have this table: uploaded = (id, filename, owner). I want to select a random filename from a given owner. Which gives me that:

"SELECT filename FROM uploaded WHERE (owner LIKE '" + owner + "' AND id IN (SELECT id FROM uploaded ORDER BY RANDOM() LIMIT 1))";

Problem is: sometimes, rs.next is false (which mean i don't have a result). However, i print the DB before searching for the filename, so i am SURE that the owner does have a related filename in this DB. Maybe only one, but still one.

This bring me to the question: what's wrong with my query?




Aucun commentaire:

Enregistrer un commentaire