CODEWITHSUNDEEP
sqlrandomms-access-2010distinct
Fred
Fred

Reputation: 493

MS Access select distinct random values

How can I select 4 distinct random values from the field answer in MS Access table question? 

SELECT TOP 4 answer,ID FROM question GROUP BY answer ORDER BY rnd(INT(NOW*ID)-NOW*ID)

Gives error message:

Run-time error '3122': Your query does not include the specified expression 'ID' as part of an aggregate function.

SELECT DISTINCT TOP 4 answer,ID FROM question ORDER BY rnd(INT(NOW*ID)-NOW*ID)

Gives error message:

Run-time error '3093': ORDER BY clause (rnd(INT(NOWID)-NOWID)) conflicts with DISTINCT.

Edit: Tried this:

SELECT TOP 4 *
FROM (SELECT answer, Rnd(MIN(ID)) AS rnd_id FROM question GROUP BY answer)  AS A
ORDER BY rnd_id;

Seems to work sofar..

Upvotes: 1

Views: 302

Answers (2)

Justin Adrias
Justin Adrias

Reputation: 401

I've creted a simple quiz application 2 years ago, and this is the query that I use to get a random question from the table.

SELECT TOP 4 * FROM Questions ORDER BY NEWID()

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270081

I suggest:

SELECT TOP 4 answer
FROM question
GROUP BY answer
ORDER BY Rnd(MIN(ID));

I don't think the subquery is necessary. And including the random value on the SELECT doesn't seem useful.

Upvotes: 1

Related Questions