Random record from a database table (T-SQL)

Question

Is there a succinct way to retrieve a random record from a sql server table?

I would like to randomize my unit test data, so am looking for a simple way to select a random id from a table. In English, the select would be "Select one id from the table where the id is a random number between the lowest id in the table and the highest id in the table."

I can't figure out a way to do it without have to run the query, test for a null value, then re-run if null.

Ideas?

Answer 1

Yes

SELECT TOP 1 * FROM table ORDER BY NEWID()

Answer 2

theres a couple of methods here

http://www.brettb.com/SQL_Help_Random_Numbers.asp

Answer 3

Also try your method to get a random Id between MIN(Id) and MAX(Id) and then

SELECT TOP 1 * FROM table WHERE Id >= @yourrandomid

It will always get you one row