CODEWITHSUNDEEP
javaandroidsqlite
Fábio Santos
Fábio Santos

Reputation: 199

SQLite random() rows but 1 of them must have a specific value?

So, I am developing a kind of Quiz game, the game will have a question on the screen and below 4 buttons, which will be filled with 1 right answer and 3 wrong answers. Simple right? I'm trying to use the following rawQuery() to load 4 answers, all of them must be random, except the right answer that has to appear.

SQLite:

myCursor = db.rawQuery("SELECT * FROM Answers WHERE QuestionID = 3 ORDER BY RANDOM() LIMIT 4", null);

So, QuestionID is the field that is specifying which question is being displayed to load the proper answers. I'll need to have a lot of answers so that the player can't memorise them. Is something like this pseudo-code possible?

myCursor = db.rawQuery("SELECT * FROM Answers WHERE QuestionID = 3 ORDER BY RANDOM('WHERE RightWrong = 1 LIMIT 1') LIMIT 4", null);

RightWrong is the field that tells if it's the right answer or not (inside the DB), it's an INT, if it is set to 1 then it is the correct answer.

I'm using SQLiteAssetHelper with SQLite DB Browser.

Upvotes: 1

Views: 98

Answers (1)

JustWannaFly
JustWannaFly

Reputation: 301

I would probably try a query along the lines of the following join. 

myCursor = db.rawQuery("SELECT * FROM (SELECT * FROM (SELECT * FROM Answers
    WHERE QuestionID = 3 AND RightWrong = 0 ORDER BY RANDOM() LIMIT 3)
    UNION SELECT * FROM (SELECT * FROM Answers WHERE QuestionID = 3 AND
    RightWrong = 1 LIMIT 1)) ORDER BY RANDOM() LIMIT 4", null)

A couple things to note, first this is not a tested query and I'm not 100% sure the SQL syntax is correct but it should help you get the idea of what I mean. Also I believe this will always put the correct answer as the last value returned and you will need to randomize where this is at a later point in your code. I hope this helps.

--EDIT-- Fixed the query so it is one that actually works

Upvotes: 1

Related Questions