.NET Answers

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

Other Related Items:

Thomas and Friends: My Red Railway Book Box (Thomas and Friends) (Bright & Early Board Books(TM))
Requested again and again, here, at last, is the Thomas Bright and Early Board Book Box Set. All four of the enormously popular Thomas board books... Read More >

Smurfette - I Heart Smurf Black T-Shirt for womenThese tees for women feature Smurfette atop a heart and read 'I Heart SMURF ' on a black background. These junior cut t-shirts are machine washable and easy to care for.

Smurfette Logo Black Long Sleeve T-Shirt for womenThese t-shirts for women feature Smurfette as Papa Smurf remade her and the 'I Heart SMURFS' trademark logo on a black background. These fun long sleeve t-shirts are machine washable and easy to care for.

Related Post

• SQL – Filtering WHERE condition on two rows
• SQL WHILE – SQL For Programmers
• Basic SQL Commands – SQL For Programmers
• SQL For Programmers – Stored Procedure Basics
• SQL SELECT CASE Instead of IIF

Bookmark to:

Hide Sites

2 Responses to “Random in SQL – SQL For Programmers”