Sample Records

[SRAB]

I've read-up on getting random records, but I need a little help.

I have a table with 200,000 SSN records. Fields: SSN, Plan Number
There are about 100 distinct plans.
How can I select a sample (random) SSN for each plan?

 

[RuralGuy]

This link may help: Create a query that returns Random records

 

[SRAB]

This link may help: Create a query that returns Random records

I ran into this link when I was searching, I do not understand how it can get me one record for each plan, it seemed it would get me 100 random records from the 200,000...but I know SQL not VBA

 

[RuralGuy]

You are of course correct. It will take a better understand of SQL than I have to answer your question.

 

[SRAB]

Well the solution for this dawned on me this weekend, and it was so simple as to be embarrasing... but I'll leave it here for others who are looking. min or max will result in only one record for each of the plans.

SELECT [Plan Number], Min([SSN]) AS MinOfSSN
FROM [TABLE_NAME]
GROUP BY [Plan Number];

 

[RuralGuy]

Thanks for posting back with your success. That does not look particulary random but if it works for you then great!

 

[SRAB]

Thanks for posting back with your success. That does not look particulary random but if it works for you then great!

Random being "any" record, it did the trick.