select random records from access

articles:

select random records from access

Here is a very simple way of selecting random records from an Access database:

SELECT TOP 5 * FROM [tableName] ORDER BY rnd(INT(NOW*id)-NOW*id)

The only prerequisite is that you have an AutoNumber Id column. It should also be noted that although this generates a random set of records, they are not truely random in a mathematical sense, but should be sufficiently random for most uses.

This method of selecting records also works in sql server, but you have to change rnd to rand.

Comments

Posted on Wednesday, February 16, 2011 4:31:00 AM GMT by panxo
this is the only code that worked for me... the others codes i had found were giving me random rows, but every time i ran the query the same rows appeared...

now with this code every time i run the query gives me a different set of rows!!

thank you very much!!!
Posted on Sunday, April 24, 2011 3:14:00 PM GMT by anonymous
many thanks. works like a charm.
Posted on Friday, July 8, 2011 11:48:00 AM GMT by Nidhin.K.V
Thanku frrnd..
its wrkng
Posted on Monday, August 1, 2011 8:13:00 PM GMT by Bob
Worked without a hitch! Thank you!
Posted on Friday, September 9, 2011 12:39:00 PM GMT by professionalsna
Thanks it Works for me
Posted on Friday, February 3, 2012 12:11:00 PM GMT by Eduardo Simoes
Cool,tnx for share
Posted on Friday, April 13, 2012 7:54:00 PM GMT by anonymous
Works for me! I tried other ways, but adding in the now twice fixed the issue I was having - the same record being chosen on load. Many thanks!
Posted on Wednesday, April 25, 2012 4:16:00 PM GMT by Frustrated Final Year Student
I could proclaim my love unto you! XD I have been trying desperately to get a random row from my database to link to my project website. Thank you so much for this!
Posted on Thursday, May 24, 2012 7:47:00 AM GMT by Kieran
Awesome! Thank you!
Posted on Wednesday, September 12, 2012 1:20:00 PM GMT by Vinicius
Tkss !

It Works !!
Posted on Friday, October 19, 2012 9:18:00 AM GMT by Isk
God bless you!
Posted on Friday, October 26, 2012 5:37:00 PM GMT by Prakash
Thanks, it worked like charm. Really appreciate for sharing this code.
Posted on Tuesday, November 6, 2012 8:48:00 PM GMT by Juan Carlos Morales
Hey Man, thanks a lot, It works perfectly
Posted on Wednesday, January 9, 2013 10:16:00 AM GMT by Santosh
Superb..
Posted on Thursday, January 10, 2013 11:33:00 AM GMT by Rafael
Perfect, thank you bro!
Posted on Friday, March 15, 2013 10:52:00 PM GMT by Florencia
Thank you, thank you!!! Works great! A code so simple and so brilliant at the same time!!!
Posted on Wednesday, April 24, 2013 11:06:00 AM GMT by biswa
working
Posted on Tuesday, May 28, 2013 1:59:00 AM GMT by Willbee
Love this - simple but effective and sure beats the other sites I looked at first that explained in detail why the problem was so complicated and then provided a complicated solution..Use of Now here is a stroke of genius
Posted on Thursday, July 11, 2013 8:18:00 PM GMT by saul smith
thaaank you!!!
perfect to get a random order for exam questions
Posted on Friday, July 26, 2013 3:38:00 PM GMT by FoundAnswer
Thank you so much! How elegant the code is! Couldn't agree more with Willbee.
Posted on Thursday, August 22, 2013 9:20:00 PM GMT by anonymous
Thank you?
I tried "ORDER BY rnd(Id)" and it returns allways the same records, random but allways the same, now it returns allways random records every time we refresh.
Posted on Sunday, September 15, 2013 2:40:00 PM GMT by winter.lin
thanks is work
Posted on Tuesday, October 22, 2013 8:48:00 PM GMT by Arturo
after some years this is the only way to get random records from an application connecting to access. Thank you.
Posted on Monday, December 2, 2013 12:21:00 PM GMT by me10ma
Awesome sql trick. I was able to add a random inspirational quotes for our data entry personal.
Posted on Thursday, December 19, 2013 7:58:00 PM GMT by anonymous
Where exactly do you put this code in Access?
Posted on Friday, February 21, 2014 3:37:00 PM GMT by Kibe
19 December: I would rather call it an sql. Put it in the sql of your query.
Posted on Thursday, March 6, 2014 9:29:00 PM GMT by anonymous
how could I add dates to this code, so randomly choose between 2/3/14 - 3/2/14?
Posted on Tuesday, April 1, 2014 11:22:00 AM GMT by anonymous
Thanks God. Finally something that really works!! :D :D :D
Posted on Friday, April 25, 2014 7:06:00 PM GMT by AnnieG
Your ROCK!!! - I tried dozens of other sites but nothing worked exactly as I needed it to UNTIL this. you are my hero!!!!!!
Posted on Saturday, May 10, 2014 4:37:00 PM GMT by arimbawa
its so long i searching this query formula,,, thanks alot.. :)
Posted on Thursday, May 22, 2014 10:44:00 AM GMT by anonymous
Thanks..!!!! Excellent work dude...keep posting
Posted on Wednesday, July 9, 2014 10:05:00 AM GMT by Agathe
Great, thanks !
Posted on Tuesday, September 9, 2014 11:05:00 AM GMT by anonymous
Great! Thanx!
Posted on Sunday, September 14, 2014 12:43:00 PM GMT by shakti
It works !..Thank You
Posted on Thursday, September 25, 2014 9:01:00 AM GMT by JH Lai
this is the only way i found to get rnd rows on aspx page when querying from database(ex. MS access). thank you so much.
Posted on Monday, October 13, 2014 12:30:00 PM GMT by Dan
This is by far and away the best solution I have come across, however it isn't infallible. I have a reasonably large dataset (~10,000+ records) and while this will return random records each time, it doesn't always return just five. around 5% of the time it will return 6, 8 or even more records. Has anyone else experienced this? Does anyone have an explanation?

Hoping to spark some interesting responses and see if I can get to the bottom of this!
Posted on Monday, October 13, 2014 1:49:00 PM GMT by Sean
Hi Dan,

This is probably because TOP 5 doesn't work in the same way as MySQL's LIMIT 0,5

TOP 5 is like the top 5 of a scoreboard, which could include ties.

The more records in your data set, the greater the chance you will have one or more ties somewhere in your top 5 scores meaning that your SELECT TOP 5 will actually return more than 5 rows.

To get around this i would try ordering by something else after your rnd(INT(NOW*id)-NOW*id)

E.g. rnd(INT(NOW*id)-NOW*id), id

While this is making the returned values even less random than i said they were originally (i.e. not truely random in a mathematical sense), the chances of having the same tie twice which you then order by their respective ids are extremely slim.

let us know if this works / or at least points your in the right direction

cheers
sean
Posted on Wednesday, April 29, 2015 10:22:00 AM GMT by Altaf
Great, its working

Thanks
Posted on Friday, December 11, 2015 4:14:00 AM GMT by Aya
Thanks duded, you save my life, I'm creating a raffle system which needed to draw randomly from table. Last time it was repeating its order and now its totally random.

Cheers!
Posted on Friday, February 26, 2016 12:21:00 PM GMT by Vinh
Thanks so much!
Posted on Wednesday, March 29, 2017 7:22:00 AM GMT by Yauhun
Excellent. This is the only way to get different values every time in my DB. Thanks very much, short but very valuable example for me.

Post a comment

name:
(optional)

email:
(optional)

comment: