Fred Fred - 4 months ago 12
SQL Question

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..

Answer

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.