2

Possible Duplicate:
Select n random rows from SQL Server table

I have database and table like that:

Persons

id  Name
--  -----
1   Jim
2   John
3   Jaimy

I want to pick one row in the result randomly. Is there any function in SQL Server?

Community
  • 1
  • 1
uzay95
  • 16,052
  • 31
  • 116
  • 182

2 Answers2

4

This might do it for you - 

SELECT TOP 1 id, Name
FROM Persons
ORDER BY NEWID()

The Random purists won't like it. It works by generating a new GUID for each row. It might also hurt performance if you have a lot of rows in the Persons table. Ideally you'd want to find a way to minimise the number of rows that you run that over first.

There is a more detailed answer in this question that might answer the question fully.

Community
  • 1
  • 1
Neil Fenwick
  • 6,106
  • 3
  • 31
  • 38
  • NEWID() Creates a unique value of type uniqueidentifier. For eg: `SELECT *,NEWID() as yuniq FROM TableName` will show every row has unique value in the 'yuniq' field. And every execution of this query will generate different values in 'yuniq' field. http://msdn.microsoft.com/en-us/library/ms190348.aspx – uzay95 Aug 22 '11 at 11:20
1

You could use something like:

SELECT TOP 1 id, Name
FROM Persons
ORDER BY NEWID()
Rowland Shaw
  • 37,700
  • 14
  • 97
  • 166