So here's the situation: I have a table with n rows, and I want to select a random row.

This one, at least superficially, may seem pretty easy, but I quickly realized there was a bit more to this than meets the eye. In this case, I was writing a quick query that displays the random quote you see under the left nav bar of my site.

I realized there are actually quite a few ways to tackle this; and like many solutions, there's isn't one best answer. It kind of depends on what you need.

So my thought process is: Let's get the bad solution out of the way first. I could simply query and return every row in a table to the client app (such as an ASP page), and then simply use a random number generator to select a number between 1 and rowcount. I won't even explain why that is simply bad, I think it's fairly obvious with a large table containing large amounts of text that this is not a good idea.

For a problem like mine, there's an easy solution. I've got a table that uses an identity column for quotes. If I've got 100 quotes in my table, I can reliably say that identity column is 1 .. 100. To get a random column, I can do something like:

This code is not too different syntactically from most random number generation. I could, then, simply select the @rand identity:

SELECT quoText as Quote, quoAuthor as Author
FROM quotes_quo
WHERE quoId = @rand

There is, though, a problem with this approach. If there's a chance your identity is not 1 to n (where n is the rowcount), your query may return nothing because the @rand may be an identity that doesn't exist. That's obviously not desirable. So, to help safeguard this, I added a quick check:

SELECT quoText as Quote, quoAuthor as Author
FROM quotes_quo
WHERE quoId = @rand

This will force the sproc to generate a random number until it finds a row, up to @maxtries (which only exists to prevent an infinite loop). This is exactly as I've done my sproc, but this is rarely the best solution me thinks.

In general, I'd only recommend doing this if you've got a fairly static table and you have a consistent identity. You need to think about the odds of a "row hit." -- if you think it will be less than 90%, this method is not for you.

I've got a few more ideas on how to do this, including one interesting SQL nugget, but I'll save those for Part II.