Random SQL Server results – a one word command

I learned a great way to randomize SQL results.

In the past, I would have followed this routine:

Run a query pulling all of the results (tens? hundreds? thousands?).
Create an array.
Loop until 5 good random results are made:
Get 1 random query result.
If it’s not in the array, put it in.
If it is in the array, ignore it and try again.
Then I’d output the data from the array, rather than the query.

I suppose I could simply fill an array then randomize it. But that’s not really the point.

As you see, with this version we have to pull back so many more results than we’re actually going to use. That’s wasteful – especially if you want to pull back lots of data in each row.

So what’s this great super duper new way I’ve discovered to simplify code, only pull back the 5 results from SQL Server, and run fast? It’s really simple: tell SQL Server to do the work for you, with one word. No post parsing needed!

Just order the results by NewID(). Yeah, that’s it.

SELECT TOP 5
	id, name, description
FROM
	companies
WHERE
	active=1
ORDER BY
	NewId()

This query will only return 5 random results – even if there are 1000 companies that are active – and in random order.  So you can simply output your query like normal with your server language. Sweet.

3 Replies to “Random SQL Server results – a one word command”

  1. Did you notice that the results are aways the same? I don’t think this method is actually random… I’m still looking for a random code, if u got any, mail me!

    Rgds!

Comments are closed.