Home  »  Code Snippets  »  SQL Server  »

How to Select Random Rows from Database Table?

 Posted By : Manoranjan SahooPosted Date : 09/03/2011 01:15:39 PM Category : SQL Server Points : 3

In SQL Server, it is quite easy to do this using the NEWID() function.  The NEWID() function creates a unique value of type uniqueidentifier.  There's no need to add a new column to your table just to have the ability of randomly selecting records from your table.  All that needs to be done is include the NEWID() function in the ORDER BY clause when doing your SELECT statement.

For Ex :

Select a random row with Microsoft SQL Server:


SELECT TOP 1 columnname FROM tablename ORDER BY NEWID()

There are different ways to select a random record or row from different databases. Here i am giving some example SQL statements for some other database server.

Select a random row with MySQL:

SELECT columnname FROM tablename ORDER BY RAND() LIMIT 1

Select a random row with PostgreSQL:

SELECT columnname FROM tablename ORDER BY RANDOM() LIMIT 1

Select a random record with Oracle:

SELECT columnname FROM ( SELECT columnname FROM tablename ORDER BY dbms_random.value )

WHERE rownum = 1

Responses
No responses found for this thread. Be the first to respond this thread.
Post Reply
Login to post Response.