Trying to select 5 random rows from table as part of testing

Question

as part of some testing I am attempting to select 5 random rows from a table, the code I'm running is:

select * 
from Table_Name
order by RAND()
Limit 5;

Upon executing the code I am presented with the error SQL command not properly ended, could someone advise on how to get this working? for reference I am using oracle SQL Developer.

Thanks in advance

Limit doesn't exist in Oracle. You need to use "fetch first 5 rows only" or use rownum or sample. See also https://stackoverflow.com/questions/9868409/how-to-get-records-randomly-from-the-oracle-database — Jonas Metzler, Oct 21 '22 at 09:06

score 0 · Answer 1 · answered Oct 21 '22 at 09:38

With sample data like this:

ID	NAME_ID
1	NAME_FOR_1
2	NAME_FOR_2
3	NAME_FOR_3
4	NAME_FOR_4
5	NAME_FOR_5
6	NAME_FOR_6
7	NAME_FOR_7
8	NAME_FOR_8
9	NAME_FOR_9
10	NAME_FOR_10
11	NAME_FOR_11
12	NAME_FOR_12
13	NAME_FOR_13
14	NAME_FOR_14
15	NAME_FOR_15

You can do it with a nested query:

SELECT 
    ID, NAME_ID
FROM   
    (
        SELECT 
            ID, NAME_ID
        FROM   
            a_table
        ORDER BY DBMS_RANDOM.RANDOM
    )
WHERE  ROWNUM <= 5;

... the result will be different every time you run the query...

/*  Result (one of)
        ID NAME_ID                                         
---------- -------------------------------------------------
        14 NAME_FOR_14                                       
         9 NAME_FOR_9                                        
        13 NAME_FOR_13                                       
        15 NAME_FOR_15                                       
         8 NAME_FOR_8
*/

Regards...

Trying to select 5 random rows from table as part of testing

1 Answers1