MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » September 2009

Re: SQL Question



fixed

Part of the rest of the referenced thread is

http://archive.midrange.com/midrange-l/200708/msg00001.html

Jim

On Wed, Sep 2, 2009 at 9:49 PM, Jim Essinger <dilbernator@xxxxxxxxx> wrote:

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?







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact