How do I get 25 random records

[rperwin]

Can someone please tell me what the best way to get a random number of records from a data set.  I am trying to have a report return 25 random claim numbers and am having a hard time finding anything useful.  I have seen some mention of a random() function but don't see much documentation on it.  Will report studio return random records or do I have to write a SQL Query that introduces the random numbers?

Any help would be greatly appreciated

Thanks

[Lynn]

Can someone please tell me what the best way to get a random number of records from a data set.  I am trying to have a report return 25 random claim numbers and am having a hard time finding anything useful.  I have seen some mention of a random() function but don't see much documentation on it.  Will report studio return random records or do I have to write a SQL Query that introduces the random numbers?

Any help would be greatly appreciated

Thanks

I would start by looking at what functions are available via the DBMS provider, although you didn't mention which database you are using.

Report Studio doesn't really "return" records...it is the database that does this as directed by whatever SQL your query generates. If you try to build something that just limits the result set to 25 records then some databases may return the same 25 records every time or may return different records, perhaps depending on physical order in the table or index/key definitions within the database. This behavior can be different from one DBMS to the next.

I suspect you'll need to define a filter in your query that somehow leverages a random number function in the database. The additional challenge will be getting something that returns 25 random numbers. I suspect many native functions are simply going to give you 1 random number so invoking it repeatedly might be challenging in set-based SQL.

Although I'm not a fan of stored procedure based query subjects (they tend to fall into the "one-trick pony" category) this may be a requirement where it makes sense. Alternatively, a database function that returns a randomly generated list of claim numbers that you can reference in a filter might make sense.

Having said all that, I've never run across this type of requirement before so perhaps someone else might chime in with an easy and elegant solution :)

[Robl]

Use this link to find how to select random N rows from a database using sql.
http://www.petefreitag.com/item/466.cfm

Create a sql Query using whatever code works.
Then inner join that to your data query on the claim number.
Although, you'll still be bringing all claim numbers back into memory in the orginal query then filtering locally.

You might be able to add the 25 random items code into the FM model somewhere, or perhaps just write the whole query in SQL where you can use the filter.