Random Rows in SQL (Part I)

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:

DECLARE @count int
DECLARE @rand int

SET @count = (SELECT count(1) from Quotes_quo)
SET @rand = Round(((@count - 1) * Rand() + 1), 0)

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:

DECLARE @count int
DECLARE @rand int

SET @count = (SELECT count(1) from Quotes_quo)
SET @rand = Round(((@count - 1) * Rand() + 1), 0)

DECLARE @maxtries int
SET @maxtries = 15

WHILE NOT EXISTS (SELECT 1 FROM quotes_quo WHERE quoId = @rand) and (@maxtries > 0)
BEGIN
    SET @rand = Round(((@count - 1) * Rand() + 1), 0)
    SET @maxtries = @maxtries - 1
END

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.
Comments are closed

My Apps

Dark Skies Astrophotography Journal Vol 1 Explore The Moon
Mars Explorer Moons of Jupiter Messier Object Explorer
Brew Finder Earthquake Explorer Venus Explorer  

My Worldmap

Month List