RE: Random selection of 10%

Exactly. They are missing percent, which is why I find their SQL implementation in this instance lacking.

-----Original Message-----
From: Charles Wilt [mailto:charles.wilt@xxxxxxxxx]
Sent: Wednesday, January 30, 2013 4:22 PM
To: Midrange Systems Technical Discussion
Subject: Re: Random selection of 10%

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.

--
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.