Re: Random selection of 10%

[Charles Wilt <charles.wilt@xxxxxxxxx>]

FETCH FIRST xxx ROWS ONLY

is the standard.  It doesn't offer a % however.

Charles


On Wed, Jan 30, 2013 at 5:15 PM, Matt Olson <Matt.Olson@xxxxxxxx> wrote:

> I wasn't able to completely figure this out in SQL on DB2 but here is my
> first attempt (it doesn't work properly).  It seems you may have to use a
> temporary table because the SQL language hasn't progressed as far as some
> of the other RDBMS's:
>
> WITH TMP AS (
> Select
>  row_number() OVER() as total, (SELECT count(*) AS Count FROM [table])
> count, [columns]
>  FROM [table]
>  )
>  SELECT * FROM tmp
>  WHERE total <= count / 10
>  ORDER BY RAND()
>
> Where [table] is your library.tablename
> And
> [columns] are the columns you wish to retrieve from the [table]
>
> This is much easier in MS SQL Server, but unfortunately DB2 does not have
> the TOP keyword:
>
> select top 10 percent * from [table] order by NEWID()
>
> would give you top 10 percent of random records.
>
> I had this very conversation a few weeks ago on why IBM DB2 doesn't have
> the TOP keyword, and their response "It's not ANSI compliant".  I told them
> if they wait to implement TOP keyword like all other databases have already
> (mySQL calls it LIMIT) then they are going to be left behind because the
> ANSI council can't keep up with technology progress, it takes them decades
> to "standardize" features people want TODAY.
>
> -----Original Message-----
> From: Bill Reger [mailto:billr@xxxxxxxxxxxxxx]
> Sent: Wednesday, January 30, 2013 10:09 AM
> To: midrange-l@xxxxxxxxxxxx
> Subject: Random selection of 10%
>
> We would like to audit 10% of the Cases that flow down the shipping
> conveyor - checking that the contents of the Case matched what was
> (supposedly) packed.
>
> Now obviously we could count each Case and pull every 10th Case for audit.
>  But it would not take long for someone to figure this out - and if they
> were so inclined - only short Cases other than the 10th in line.
>
> Is there an algorithm/logic that we could employ to randomly pull Cases
> that would equate to 10% over the course of a day but not be every 10th
> Case?  (My logic brain is on strike today and I can't think of a way to
> design this.)
>
> Bill
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
> unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
> moment to review the archives at http://archive.midrange.com/midrange-l.
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.