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 (
row_number() OVER() as total, (SELECT count(*) AS Count FROM [table]) count, [columns]
SELECT * FROM tmp
WHERE total <= count / 10
ORDER BY RAND()
Where [table] is your library.tablename
[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.
From: Bill Reger [mailto:billr@xxxxxxxxxxxxxx]
Sent: Wednesday, January 30, 2013 10:09 AM
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
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.)
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,
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l