Randomly Select a Row with SQL in Access

Question

I have a small access database with some tables. I am trying the code in the sql design within access. I just want to randomly select a record within a table.

I created a simple table called StateAbbreviation. It has two columns: ID and Abbreviation. ID is just an autonumber and Abbreviation are different abbreviations for states.

I saw this thread here. So I tried

SELECT Abbreviation
FROM STATEABBREVIATION
ORDER BY RAND()
LIMIT 1;

I get the error Syntax error (missing operator) in query expresion RAND() LIMIT 1. So I tired RANDOM() instead of RAND(). Same error.

None of the others worked either. What am I doing wrong? Thanks.

"small access database" ? Please tag your question accordingly — Yuriy Galanter, Nov 08 '13 at 21:44
If you use MS-Access, see this: http://stackoverflow.com/questions/9937222/how-to-get-random-record-from-ms-access-database — ypercubeᵀᴹ, Nov 08 '13 at 21:44

score 3 · Answer 1 · answered Nov 08 '13 at 21:54

3

Ypercude provided a link that led me to the right answer below:

SELECT TOP 1 ABBREVIATION
FROM STATEABBREVIATION 
ORDER BY RND(ID);

Note that for RND(), I believe that it has to be an integer value/variable.

answered Nov 08 '13 at 21:54

Note also that is is important to provide the integer primary key value as an argument (`RND(ID)`) so that Access SQL will generate a different random value for each record. (If you simply use `ORDER BY RND()` then the query optimizer considers the function call to be deterministic and only evaluates it once. Therefore you get the same "random" value for all rows.) – Gord Thompson Nov 09 '13 at 00:07

score 2 · Answer 2 · answered Oct 09 '16 at 13:37

You need both a variable and a time seed to not get the same sequence(s) each time you open Access and run the query - and to use Access SQL in Access:

SELECT TOP 1 Abbreviation
FROM STATEABBREVIATION
ORDER BY Rnd(-Timer()*[ID]);

where ID is the primary key of the table.

score 0 · Answer 3 · edited Oct 09 '16 at 10:51

Please try this, it is helpful to you

It is possible by using a stored procedure and function, which I created it's have a extra column which you could be create in your table FLAG name and column all field value should be 0 Then it works

create Procedure proc_randomprimarykeynumber
as
    declare @Primarykeyid int  

    select top 1  
        @Primarykeyid = u.ID 
    from 
        StateAbbreviation u  
    left join  
        StateAbbreviation v on u.ID = v.ID + 1 
    where 
        v.flag = 1 

    if(@Primarykeyid is null )
    begin
        UPDATE StateAbbreviation 
        SET flag = 0  

        UPDATE StateAbbreviation 
        SET flag = 1 
        WHERE ID IN (SELECT TOP 1 ID 
                     FROM dbo.StateAbbreviation)
    END
    ELSE
    BEGIN
        UPDATE StateAbbreviation 
        SET flag = 0 

        UPDATE StateAbbreviation 
        SET flag = 1 
        WHERE ID IN (@Primarykeyid)
    END 

    SET @Primarykeyid = 1 

    SELECT TOP 1 
        ID, Abbreviation 
    FROM
        StateAbbreviation 
    WHERE
        flag = 1

It is made in stored procedure run this and get serial wise primary key

exec proc_randomprimarykeynumber

Thanks and regard

score -1 · Answer 4 · answered Nov 08 '13 at 21:48

-1

Try this:

SELECT TOP 1 *
FROM tbl_name
ORDER BY NEWID()

Of course this may have performance considerations for large tables.

answered Nov 08 '13 at 21:48

timbck2

1,036
3
15
36

2

It will have "performance considerations" under Access SQL in every case, specifically "Undefined function 'NEWID' in expression." – Gord Thompson Nov 09 '13 at 00:12
Oops - I didn't notice the word "Access" in the OP - I just assumed SQL Server. – timbck2 Nov 12 '13 at 22:12

Randomly Select a Row with SQL in Access

4 Answers4

Linked

Related