Superb! And so simple!

This technique is really awesome, I have been using this in my Promotion project to get winners randomly from data from last couple of years.

Hmmhh, I was about to be clever and generate some random data for a TSQL Challenge only to find that this NEWID() critter is truly random. It's not like RAND() where you can initially invoke RAND(1) and then get the same random numbers in subsequent invocations of RAND(). I guess NEWID() is meant to be truly random and non repetitive - no matter what your needs :-)

nice tip! Although the sql needs to be

SELECT TOP(1) * FROM sometable ORDER BY NEWID() or

SELECT TOP(1) columnlist FROM sometable ORDER BY NEWID()

vanne040 of course, you're right. Already edited.

NEWID() is evaluated for each resulting rows while RAND() is evaluated for the whole resultset.

It's really important to know NEWID is evaluated afterward in order to understand what happens when you try this :

SELECT
    *
FROM (VALUES(1), (2)) AS X(x)
CROSS JOIN (VALUES (NEWID())) AS Y(y)

Sergejack, ...RAND() is evaluated for the whole resultset... Yes that's the key to this issue.

Nothing is wrong. Just confirmed what you said.

I think this tip got repeated. Previous tip here: http://img1.beyondrelational.com/modules/1/justlearned/388/tips/8579/select-a-random-record-from-a-table.aspx

Here's how I usually query for a random record:

SELECT top 1 AccountNumber 
FROM
         (SELECT  a.AccountNumber ,
                  RandomNumber = RAND(CAST(NEWID() AS BINARY(6)))
          FROM AccountTable a
          WHERE AcctStartDate >= '1/1/2012') as  vt
ORDER BY vt.RandomNumber

I'll usually plug this into a declare @variable int... set @variable = (Select.... ) for testing out a parameter on a report.

Why not plain NEWID()? Why are you enclosing it in RAND()?

Datta, I bet it is, but I didn't manage to find it before, so I just wanted to share!

dishdy, I believe that in Elizabeths query was, originally, just RAND, and - maybe - NEWID() was added in next steps.