Re: SQL Question

Dennis,

I did get a reply from Charles Wilt.

A version of his suggestion worked well to do what I needed. I'm not sure
what happened to his and Elvis's reply to my last message in the archives.
I see them when I search my gmail archived messages.

Here is Charles suggestion, with one minor edit fix added by Elvis
Budimlic;

With
T1 as (select status, infoField, rand() as randomNbr
from filename
),
T2 AS (SELECT Status, InfoField,
ROW_NUMBER() over (PARTITION BY Status ORDER BY Status, randomNbr) as
Nbr
from T1 ORDER BY Status )

SELECT * FROM T2 WHERE Nbr < 6

Chuck Pence added the following words of wisdom/warning;

Yes that should work, but to those for which it might not be obvious,
with the _caveat_ that T1 generates a temporary table of all rows in the
original file [of the named fields & expression]. From the original
description it would not seem to be an issue [given there might not even
be five rows for any one status], but... The number of rows might make
such an implementation prohibitive for some tables and/or environments.

For very large result sets, an alternate method might be to use an
external table function which returns a given number of rows from that
table for a specific key value. That program could use row level
access, to decide what rows to return. Some index might be available
and conducive to generating a relatively quick sampling; although
/random/ probably not nearly as easy as with rand() and ordering. If
the program is in its own activation group and can leave the file open
for the repeated calls for each status, the file would be opened only
for that named activation, thus avoiding open overhead.


HTH

Jim



On Wed, Sep 2, 2009 at 6:37 PM, Dennis Lovelady <iseries@xxxxxxxxxxxx>wrote:

I asked a very similar question a while back. It may help you get to
the random result set you need. Mine was for some auditors at a
previous company.

Interesting. I just looked through that thread, and it looks like you got
no reply to the final question. Wouldn't a change in ORDER BY help? Not
random, I realize, but may be able to mix things up a bit?