Randomly Select a Row with SQL in Access

How to get random record from MS Access database

The following will get a random questionID from your table

MySQL

SELECT questionID FROM questions ORDER BY RAND() LIMIT 1

MS Access

SELECT top 1 questionID from questions ORDER BY rnd(questionID)

How to randomly select rows in SQL?

SELECT TOP 5 Id, Name FROM customerNames
ORDER BY NEWID()

That said, everybody seems to come to this page for the more general answer to your question:

Selecting a random row in SQL

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2

SELECT column, RAND() as IDX 
FROM table 
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

Select a random row with sqlite:

SELECT column FROM table 
ORDER BY RANDOM() LIMIT 1

How to randomly select a certain percent of rows in Access with another condition?

Since you are already selecting a random portion, the question is really just about selection criteria involving a "total". The key here is that you need another query, an aggregate query. The other query can be either another saved query, an embedded subquery, or a call to a function which performs the query.

Using a subquery to get the total:

SELECT TOP 10 PERCENT * 
FROM Students 
WHERE StudentType='girl' 
    AND (Students.[Spent] / (SELECT SUM(S2.[Spent]) FROM Students As S2) = 0.30)
ORDER BY rnd(ID)

Make sure to add a different alias to the same table, since Access can get confused if the subquery has a table with the same name as the main query. The question did not mention the "amount spent" column so I just guessed. This also assumes that "both groups" is essentially the same as "all student records". If that's not the case then you could add to the subquery WHERE S2.StudentType In ('girl', 'boy').

Using a domain aggregate function:

SELECT TOP 10 PERCENT * 
FROM Students 
WHERE StudentType='girl' 
    AND (Students.[Spent] / DSum("[Spent]", "Students", "") = 0.30)
ORDER BY rnd(ID)

Using another saved query:

First create and save the separate aggregate query as [Summed]:

SELECT SUM(S2.[Spent]) As TotalSpent FROM Students As S2

Now do a cross join so that each row is paired with the total:

SELECT TOP 10 PERCENT * 
FROM Students, Summed
WHERE StudentType='girl' 
    AND (Students.[Spent] / Summed.TotalSpent = 0.30)
ORDER BY rnd(ID)

The efficiency of each solution may vary. For a small table of students it might not matter. If it does become an issue, I have found that the Domain Aggregate functions are not very efficient even though they appear to be simpler to use. More powerful query engines (not Access) are often better at analyzing a query plan and automatically reducing redundant calculations, but with Access you have to plan that out yourself.

Last note: If you have more complicated grouping, any of the solutions will have additional join conditions. For instance, if the aggregate query also had a GROUP BY clause on an ID, then instead of a cross join, you'd now want an INNER JOIN matching the ID in the main table. In the case of the domain aggregate function, you'd want to specify a criteria parameter that refers to a table field value. The point is that the above examples are not a precise template for all cases.

Query for retrieving random records from MS Access

Assuming that in a table MyTable you have a primary key ID field in a that is an autoincrement integer, you can do something like this to retrieve, say 10 random records from MyTable:

SELECT Top 10 *
FROM   (SELECT *,
           Rnd(ID) AS RandomValue
        FROM   MyTable)
ORDER  BY RandomValue 

Edit:
Found another similar answer: How to get random record from MS Access database

How to request a random row in SQL?

See this post: SQL to Select a random row from a database table. It goes through methods for doing this in MySQL, PostgreSQL, Microsoft SQL Server, IBM DB2 and Oracle (the following is copied from that link):

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2

SELECT column, RAND() as IDX 
FROM table 
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

---

Related Topics