MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » January 2013

RE: Random selection of 10%



fixed

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.






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