Random in SQL – SQL For Programmers

IMG_1382 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

Like this Article? Subscribe to get every article sent to your email.

Related Post

  • Basic SQL Commands – SQL For ProgrammersBasic SQL Commands – SQL For Programmers Before we get into the specifics of the commands that can be used within a stored procedure, I think it would be helpful to review some of the more basic commands that we can use that […]
  • SQL For Programmers – Stored Procedure BasicsSQL For Programmers – Stored Procedure Basics Now that we've established a reason for using them, let's continue with the basic syntax of the stored procedure. I think you'll find that it is not that dissimilar to writing a procedure […]
  • SQL WHILE – SQL For ProgrammersSQL WHILE – SQL For Programmers The IF statement we looked at on Tuesday was pretty tame compared to the WHILE construct.Actually, the main thing you need to keep in mind is that WHILE is all you have.  There is no […]
  • SQL – Filtering WHERE condition on two rowsSQL – Filtering WHERE condition on two rows I received the following question a couple of days ago from a programmer using the "Ask  a Question" form which you can access from the menu of this blog:Given the following […]
  • SQL For Programmers – Finding IN a ListSQL For Programmers – Finding IN a List In the last SQL post, we looked at looking for content that was LIKE other content.  While this has its uses, it is limited in its ability to find more than one pattern.  So what if we […]

About Dave Bush

Dave Bush is a .NET programmer and Certified ScrumMaster who is passionate about managing risk as it relates to developing software. When he is not writing or speaking about topics related to Application Lifecycle Risk Management (ALRM), he is an example to his peers as he develops web sites in the ASP.NET environment using industry best practices.

2 Pingbacks/Trackbacks