The Random Contestant Picker in under 5 Minutes
At the Raleigh Code Camp things went pretty well. There of course were things here and there that did not go according to plan, one of those was how are we going to select door prize winners at the end of the day?
Richard Campbell had offered to conduct a 64-Bit question contest for us to make door prizes entertaining. But one problem, how do we randomly decide who actually wins? So I quickly thought about importing a list of attendees to a SQL Server table in my local instance of SQL Server Express. Both of us knew there was a way to select random rows, but were not sure how. So Richard and I worked on solving the problem together.
I could get the attendee list in a CSV file, which allowed us to perform a Bulk Insert statement. The list was pretty simple, so I created a simple four column table with nvarchar data types. The fields were FirstName, LastName, EMail and RegistrationDate. Then ran the following SQL Statement to insert the records:
bulk insert contestants
With (fieldterminator =',')
The official documentation for the Bulk Insert can be found on the MSDN library. It allows you to insert a CSV file to a table. This is a pretty simple statement where you call 'Bulk Insert' followed by the destination table name. The from clause specifies the CSV file to be imported. This will need to be on a path the SQL Server itself can access.
The final statement is WITH. In our case we needed to specify the fieldterminator as a ','. There are many other attributes that can be set in the WITH statement, but for our purposes this was all we needed.
Meanwhile Richard worked on finding the syntax for selecting a random record from a table in SQL Server. About 20 seconds later he had the answer:
select top 1 FirstName, LastName
order by newid()
This is a neat trick where you select one record, and in our case limit it to a First and Last name. The Order By statement specifies a NewId() function. NewId returns a GUID, which I have no clue how order would be determined. The point is this is generated by the select statement for each row and the SQL engine figures this out for us.
So in about 5 minutes we produced a workable solution to randomly select contestants for the 64-bit Question contest. It is nice to have cool technology available at our fingertips to solve problems in a very simple way. Plus it was pretty cool to work on a little project with a cool guy! Pair programming as it should be.