How to correctly use syntax for RAND()

Question

keep getting error when it try to select 4 random rows. not sure i got the snytax right for doing this. Also note i am using an access db. Also note.. ManaTypes is an array of strings.

with adoquery1 do
begin
  close;
  sql.Clear;
  sql.Add('SELECT * ');
  sql.Add('FROM Cards ');
  sql.Add('WHERE Color='+ManaTypes[i]+' ORDER BY RAND() Limit 4');
  open;
end;

Getting error 'snytax error (missing operator ) in query expression 'Color="

yes of course :D also ORDER BY Rnd Limit 4 did not work either same error — Glen Morse, Oct 21 '13 at 10:37
That question was asked over a year ago and does not supply answer.. how did you even find it lol.. — Glen Morse, Oct 21 '13 at 11:14
Hi Glen, I added an answer to http://stackoverflow.com/questions/9937222/how-to-get-random-record-from-ms-access-database — bummi, Oct 21 '13 at 11:19
I really hope it dont get closed, as the DUPE question has wrong answer.. — Glen Morse, Oct 21 '13 at 12:02
Proper way would be deleting this question and adding a bounty to that duplicate. I would set a bounty there, but I don't have time to test the answers since I don't have time nor Access (to be more precise even don't want to have Access). — TLama, Oct 21 '13 at 12:07
its been updated there... i just hope someone changes the answer to the correct answer...in which case i dont care if htis is now deleted — Glen Morse, Oct 21 '13 at 12:11

juergen d · Answer 1 · 2013-10-21T10:45:57.803

2

Try

sql.Add(
   'SELECT Top 4 *
    FROM
    (
       SELECT *, Rnd(ID) AS RandomValue
       FROM Cards 
       WHERE Color = "' + ManaTypes[i] + '"
    )
    ORDER  BY RandomValue'

edited Oct 21 '13 at 10:45

answered Oct 21 '13 at 10:38

juergen d

201,996
37
293
362

That worked or at least ran.. but had to swap the ' and " around – Glen Morse Oct 21 '13 at 10:41
1

also it seems to give the same 4 random ones each time? – Glen Morse Oct 21 '13 at 10:42
Still works, but still same 4 random ones.. does random not work like i think it should? – Glen Morse Oct 21 '13 at 10:52

How to correctly use syntax for RAND()

1 Answers1