Returning n Random Records from a Table - SQL

Problem

You want to return a specific number of random records from a table. You want to modify the following statement such that successive executions will produce a different set of five rows:

 select ename, job
   from emp

Solution

Take any built-in function supported by your DBMS for returning random values. Use that function in an ORDER BY clause to sort rows randomly. Then, use the previous recipe's technique to limit the number of randomly sorted rows to return.

DB2

Use the built-in function RAND in conjunction with ORDER BY and FETCH:

 1 select ename,job
 2   from emp
 3  order by rand() fetch first 5 rows only

MySQL

Use the built-in RAND function in conjunction with LIMIT and ORDER BY:

 1 select ename,job
 2   from emp
 3  order by rand() limit 5

PostgreSQL

Use the built-in RANDOM function in conjunction with LIMIT and ORDER BY:

 1 select ename,job
 2   from emp
 3  order by random() limit 5

Oracle

Use the built-in function VALUE, found in the built-in package DBMS_RANDOM, in conjunction with ORDER BY and the built-in function ROWNUM:

 1 select *
 2   from (
 3  select ename, job
 4    from emp
 6   order by dbms_random.value()
 7        )
 8   where rownum <= 5

SQL Server

Use the built-in function NEWID in conjunction with TOP and ORDER BY to return a random result set:

 1 select top 5 ename,job
 2   from emp
 3  order by newid()

Discussion

The ORDER BY clause can accept a function's return value and use it to change the order of the result set. The solution queries all restrict the number of rows to return after the function in the ORDER BY clause is executed. Non-Oracle users may find it helpful to look at the Oracle solution as it shows (conceptually) what is happening under the covers of the other solutions.

It is important that you don't confuse using a function in the ORDER BY clause with using a numeric constant. When specifying a numeric constant in the ORDER BY clause, you are requesting that the sort be done according the column in that ordinal position in the SELECT list. When you specify a function in the ORDER BY clause, the sort is performed on the result from the function as it is evaluated for each row.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table