MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » January 2013

Re: Random selection of 10%



fixed

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.







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