CODEWITHSUNDEEP
sqlms-access
user2781018
user2781018

Reputation:

Randomly Select a Row with SQL in Access

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.

Upvotes: 1

Views: 2294

Answers (4)

Gustav
Gustav

Reputation: 55906

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.

Upvotes: 2

code save
code save

Reputation: 1106

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

Upvotes: 0

user2781018
user2781018

Reputation:

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.

Upvotes: 3

timbck2
timbck2

Reputation: 1066

Try this:

SELECT TOP 1 *
FROM tbl_name
ORDER BY NEWID()

Of course this may have performance considerations for large tables.

Upvotes: -1

Related Questions