Random in SQL – SQL For Programmers

November 04, 2008 By: Dave

Last week I had an interesting assignment: Given a specific record, randomly retrieve three related records from the database.

Naturally, as a programmer, I started looking for the SQL random function. Which I found.

But even as I was searching for how to use that function, I was thinking to myself, “How am I going to structure things in such a way so as to randomly select the records once I have the function? It isn’t like I can retrieve the records into an array and then select them out. Am I going to have to retrieve them into a temp table?”

And that’s when I stumbled onto a slick little trick. The NewID() function.

The NewID() function returns a value that looks something like a GUID. The beauty of this function is that when you use it as a column in your return set and set the order to that column, you end up with a randomly sorted list of records.

So what I thought was going to be lines and lines of SQL code ended up being a rather simple select statement:

SELECT TOP 3 field1, field2, NewId as OrderId
    FROM databaseTable
    WHERE @filter = filter
    ORDER BY OrderId

Other places talking about NewId():

Generate a unique number in Sql Server 2005 – Let us quickly take a tour of how to do that using Sql server 2005. SQL Server contains the NEWID() function. This function creates a unique value of type uniqueidentifier. We can use this function in several ways to generate unique …

Identity vs uniqueidentifier – You have to use “NEWID()” SQL function to create a new GUID and then assign it to the column which is uniqueidentifier type. uniqueidentifier type is good since GUID’s are unique in space and time. So even if you merge two tables with …

Generating Random Data for SQL Server and MySQL – SQL Server – uses NEWID() in ORDER BY. 1: — using the AdventureWorks Database 2: SELECT 3: TOP 10 4: DepartmentID, 5: [Name] 6: FROM 7: HumanResources.Department 8: ORDER BY 9: NEWID() 10:. MySQL – uses RAND() in ORDER BY. 1: SELECT