27.2. Picking Random Rows from a Table

The answer is that, basically, you cannot directly pick a set of random rows from a table in SQL. There is no randomize operator in the standard, and you don’t often find the same pseudo-random number generator function in various vendor extensions, either.

Picking random rows from a table for a statistical sample is a handy thing, and you do it in other languages with a pseudo-random number generator. There are two kinds of random drawings from a set, with or without replacement. If SQL had random number functions, I suppose they would be shown as RANDOM(x) and RANDOM(DISTINCT x). But there is no such function in SQL, and none is planned. Examples from the real world include dealing a poker hand (a random ...

Get Joe Celko's SQL for Smarties, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.