FETCH FIRST xxx ROWS ONLY
is the standard. It doesn't offer a % however.
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 (
row_number() OVER() as total, (SELECT count(*) AS Count FROM [table])
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
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.
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