Hi all SQLers!
Last week I’ve found this quiz from my consultant customer. He has a scenario to let me help as a short describe here.
How do I select some records from SQL Server randomly?
It seems to be an easy question but what we can do if SQL Server doesn’t directly support this functionality! So, I decide to read on SQL Server’s online help to find out the solution and result on SQL Server don’t have any function that can select records randomly.
Now I’m thinking about Joe Celko, the person who you know when thinking about SQL puzzle or quiz. Just thinks to him but didn’t try to contact him cause I still believe this quiz gonna be too easy for him to implement.
So, I just continue on research…
After that, I seen many various methods to implement this functionality but most of them are implement at application level. Means your application just select all records and randomize some number then select the records randomly by indexing access. That I thinks not efficiency. Certainly, you feel the same as me or you’ll not reading until this. ^_^
In last weekend, while I’m finding for the best solution for this case. My friend come along to my home and talking about SQL Server and blah blah blah… and then he gave me an idea to solve this case. For god sake, this is a solution not an idea!
How the solution be and how it works?
The key just stay in NEWID() function!
For all people who don’t know what is NEWID() function. This is a built-in function in SQL Server that will generate a unique string which will not the same on each call. Just try it in SQL Server by executing this command. “SELECT NEWID()“.
Gotcha! It’s what someone called “unique identifier”, “GUID” or somethings else.
So now, how can I use this function to select records randomly?
It’s easy as someone may already clear on this case when I said on NEWID(). Just use it in ORDER BY clause!
SELECT * FROM Books ORDER BY NEWID()
You got it now. How easily!
If you enjoyed this article, please consider sharing it!
