Top n by Group

[joshk6656]

would anyone mind helping me with the query below. i am looking to pool a table and to pull a set number of records randomly for each processor. from what i am understanding, i need to calculate a rnd() field, then sort by that and use the top query, but i am having trouble. any help would be appreciated

Say the fields are:
ID (auto number) | Audit Date | Processor | Claim Number (not unique)

the type of results im looking for is...
1 | 1/1/2008 | Josh | 1234
900 | 1/1/2008 | Josh | 3424
500 | 1/1/2008 | Josh | 7567
600| 1/1/2008 | Josh | 1456
450 | 1/1/2008 | Josh | 8764
345 | 1/1/2008 | Chris | 12435
987| 1/1/2008 | Chris | 16768
945 | 1/1/2008 | Chris | 65425
146 | 1/1/2008 | Chris| 143245
234| 1/1/2008 | Chris | 765363

 

[namliam]

Step 1
Add a field with the RND function in it, sort on this field. Save this query as QueryRND

Step 2
Create a new query,
Select top 10 * from queryRND

Done

 

[joshk6656]

That would indeed pick a random 10 from the entire table, but I need to pick a random number for each processor

 

[namliam]

Select top 10 * from queryRND where Processor = "Chris" Union
Select top 10 * from queryRND where Processor = "Josh"

Kindoff hard to maintain if the processors change... You could write some code to create this union query for each processor.