By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,066 Members | 1,200 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,066 IT Pros & Developers. It's quick & easy.

select every 79th records

P: 333
I have 400K records on my Access database. Now I wanted you pull out randomly records that falls on 79th records from top to bottom. How can you do that on sql?

Your help is much appreciated.
May 15 '09 #1
Share this Question
Share on Google+
7 Replies

Expert Mod 2.5K+
P: 2,545
SQL is a language which implements manipulation of relational database tables. These tables are based on set arithmetic, where there is no intrinsic concept of record position. It is not possible to select the Nth record in a sequence in SQL - and if it was, it would be a sequential selection, not a random one.

If you want to select a random sample of your 400k records you will need to assign a random number to each using a query. You can't just use the Rnd function in the query, as it would be evaluated just the once and return the same value for every row. But with a little ingenuity you can call a function, supplying the value of one of your table fields as an argument to the function to force re-evaluation of the random value. An example of such a function is listed below.

Expand|Select|Wrap|Line Numbers
  1. Public Function fGenerateRandomNumber(somefield As Variant) As Single
  3. 'This function generates a random number using the rnd function.
  4. 'The value of somefield passed to the function is not otherwise used -
  5. 'it simply forces the random number to be generated on every call to
  6. 'the function, whereas if Rnd is called in a query
  7. 'it is only evaluated once.
  8. '
  9.     fGenerateRandomNumber = Rnd()
  10. End Function
I have tested this on an ordered sequence of numbers from 1 to 4000. If we assign a random number to each row the rows can then be ordered by that value and a selection made from the whole set. In your case, with 400k records, to select 1 in 80 (perhaps why you are looking for every 79th) you can select, say, the first 5000 records to be your random sample.

Note that the final value of the random number displayed will not match the ordering expected, as ordering will force reevaluation of the random numbers. It is nevertheless randomly ordered, as you will see in the sample from the 4000 test records below:

Expand|Select|Wrap|Line Numbers
  1. No    Random
  2. 3183    0.623072385787964
  3. 3557    0.323390901088715
  4. 1594    0.144493460655212
  5. 823    0.102511465549469
  6. 3491    0.401980400085449
  7. 2221    0.670504748821259
  8. 1706    0.301638245582581
  9. 3037    0.285618126392365
  10. 636    0.139037370681763
  11. 9    0.850202977657318
  12. 2981    0.839936137199402
  13. 1225    0.688546717166901
  14. 1106    0.126784801483154
  15. 3422    0.547361075878143
  16. 3107    1.72606706619263E-02
  17. 3255    0.600176632404327
  18. 540    0.311084508895874
The query used for this random sample set is very simple:

Expand|Select|Wrap|Line Numbers
  1. SELECT No, fGenerateRandomNumber([No]) AS Random
  2. FROM TestTable
  3. ORDER BY fGenerateRandomNumber([No]);
Note also that you will get a different sample every time you run the query - as no two runs will generate the same set of random numbers.

May 16 '09 #2

Expert Mod 2.5K+
P: 2,545
Oh, I forgot to mention two things:

Firstly, the function should be placed in any public code module (one that is visible from the Modules tab in Access), or in a new module if one does not already exist.

Secondly, if you really do need to implement some form of 'select the Nth record' routine and not a random sample this can be done in VBA code using a recordset and processing through the records under program control (for example, to place the primary key values of the selected records in another table for later joining back as the selection set). However, I would advise you again that repetitively selecting the Nth record in some sequence is NOT random sampling at all.

May 16 '09 #3

Expert 5K+
P: 8,679
A lot of what I am referring to has already been stated by Stewart, so I'll not state it again. There is a little known Method of retrieving a Random Set of Rows from a Table or Query by using the previously mentioned Public Function approach and the Query's Top Value Property. If you are interested, just let me know, and I'll demonstrate how it is done.

The concept itself is rather quite simple. You add a new Column to the Query, fill it with a List of Random Numbers, and retrieve the Top N Rows, where N is a Number between 1 and the Number of Rows in the underlying data. By specifying the Top N Values for the Query, you are forcing Access to return that many Rows as the result set to the Query. You can also return a certain Percentage of the Total Rows by setting the Top Value to a Percentage (Random Sample of 37% of the Total Number of Records).
May 16 '09 #4

P: 333

Please show me how to put it all together.

May 18 '09 #5

Expert 5K+
P: 8,679
This Demo will display 5 Random Employees, and all their related Fields, from the Employees Table of the Northwind Sample Database:
  1. Open the Northwind Sample Database or parallel this Steps in your own Database.
  2. Create the following 'Public' Function fGenerateRandom() and pass to it any Field as long as it is contained within the Employees Table. Stewart has already given an excellent explanation as to why this is necessary in Post #2.
    Expand|Select|Wrap|Line Numbers
    1. Public Function fGenerateRandom(varField As Variant)
    2.   Randomize
    3.   fGenerateRandom = Rnd()
    4. End Function
  3. Create the following Query by Copying-N-Pasting the SQL Statement posted below into the View ==> SQL View Pane of the New Query (Design Mode).
    Expand|Select|Wrap|Line Numbers
    1. SELECT TOP 5 Employees.*
    2. FROM Employees
    3. ORDER BY fGenerateRandom([LastName]);
  4. Switch to Design View to see the Graphical representation of the Query in the Query Grid.
  5. Look at the Top Values Property of the Query to see the relevance of the 5.
  6. Every time you execute this Query, you will see a Random Selection of 5 Employees along with accompanying Fields. The Random Number Field is intentionally Hidden since it serves no purpose displaying it.
  7. Any questions feel free to ask, and one of us will get back to you with an answer.
May 18 '09 #6

P: 333
It works! Thanks for showing it to me Adezii your help is much apreciated and thanks to Stewart for the explaining how it process.

May 19 '09 #7

Expert 5K+
P: 8,679
Glad the old Stew/ADezii combination worked for you (LOL)!
May 19 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.