473,322 Members | 1,287 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Random Selection Of Records

Tom
Say I have a field named Numbers in MyTable and the field contains 500
random numbers between 1 and 1000; no duplicates. How do you write a query
to randomly return 50 records with no duplicates from the 500 records in the
table?

Thanks!

Tom
Nov 13 '05 #1
2 1634
Try this query:
SELECT TOP 50 MyTable.*
FROM MyTable
ORDER BY Rnd(MyTable.MyID), MyTable.MyID;

Before running it you need to execute a Randomize statement so Rnd() does
not use a repeatable sequence.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom" <tm*****@nospam.please> wrote in message
news:3D*****************@newsread1.news.pas.earthl ink.net...
Say I have a field named Numbers in MyTable and the field contains 500
random numbers between 1 and 1000; no duplicates. How do you write a query
to randomly return 50 records with no duplicates from the 500 records in
the
table?

Nov 13 '05 #2
On Fri, 21 Jan 2005 02:17:35 GMT, "Tom" <tm*****@nospam.please> wrote:
Say I have a field named Numbers in MyTable and the field contains 500
random numbers between 1 and 1000; no duplicates. How do you write a query
to randomly return 50 records with no duplicates from the 500 records in the
table?


Use the Top Values property:

SELECT TOP 50 *
FROM MyTable
ORDER BY [Numbers];

If you mean no duplicates *between runs of the query* then it's much
more difficult!

John W. Vinson[MVP]
Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Keith Griffiths | last post by:
I'm trying to do a search under a set criteria followed by a selection of random entries meeting this criteria. But I don't seem to be able to achieve this. The idea being to search on say...
3
by: Andie | last post by:
Hello All, How would I go about using a disconnect recordset and select (x) records from it, x being the number of records to be selected. Many thanks in advance. -- Andie
5
by: Jamie Fryatt | last post by:
Hi all, im a little bit new to all this so i you could help me a little i would be greatful. How do i select a completly random record from a database? sql = select (random) from tbl, sort of...
3
by: jaYPee | last post by:
anyone know how can i get a 10% random records out of 2000 records? 2000 is only an example and it will vary anytime. i want to create a report out of this 10% thanks in advance
2
by: IceCube | last post by:
Hello, I would like to select/filter at random 30 records out of an Access-table of 1500 records. I know the option "Top" which gives me the possibility to see the 30 first records of the table...
2
by: VB Programmer | last post by:
I am interested to hear your suggestions on this... I have a table full of survey questions. The questions are individually classified as priority 1, 2 or 3. (Priority 1 means the question...
2
by: RJN | last post by:
Hi I have to randomly pickup records from database for some quality check of data. Say the data base has about 1000 records, I have to randomly select about 50 records. Can someone suggest me...
19
by: Boris Borcic | last post by:
does x.sort(cmp = lambda x,y : cmp(random.random(),0.5)) pick a random shuffle of x with uniform distribution ? Intuitively, assuming list.sort() does a minimal number of comparisons to ...
3
by: Cindy | last post by:
I'm trying to use the NEWID function in dynamic SQL and get an error message Incorrect syntax near the keyword 'ORDER'. Looks like I can't do an insert with an Order by clause. Here's the code:...
3
by: Laphan | last post by:
Hi All I use a MySQL DB with my ASP classic web app. I've been asked if I can create a routine whereby I get a random number of products (records) from the DB and display these on the site. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.