How to Select a random row from a database table


The Spit

JF-Expert Member
Joined
Feb 3, 2009
Messages
418
Likes
49
Points
45

The Spit

JF-Expert Member
Joined Feb 3, 2009
418 49 45
SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1;

e.g
SELECT UserName FROM
( SELECT UserName FROM userInfo
ORDER BY dbms_random.value )
WHERE rownum = 1;

UserInfo is the name of my table.
UserName is one of the columns in this table.

you can run it in sqlplus.
 

Nipigie

Senior Member
Joined
Nov 2, 2010
Messages
121
Likes
2
Points
0

Nipigie

Senior Member
Joined Nov 2, 2010
121 2 0
Thanks jakathesi, it works, but i want now to select 10 columns randomly but must be superior to certain value. your help please.
 

The Spit

JF-Expert Member
Joined
Feb 3, 2009
Messages
418
Likes
49
Points
45

The Spit

JF-Expert Member
Joined Feb 3, 2009
418 49 45
Thanks jakathesi, it works, but i want now to select 10 columns randomly but must be superior to certain value. your help please.
with ten columns you can go with

SELECT column1,column2,column3,.... column10 FROM
( SELECT column1,column2,column3,.... column10 FROM Table_Name
ORDER BY dbms_random.value )
WHERE rownum = 1;
 

Nipigie

Senior Member
Joined
Nov 2, 2010
Messages
121
Likes
2
Points
0

Nipigie

Senior Member
Joined Nov 2, 2010
121 2 0
with ten columns you can go with

SELECT column1,column2,column3,.... column10 FROM
( SELECT column1,column2,column3,.... column10 FROM Table_Name
ORDER BY dbms_random.value )
WHERE rownum = 1;
THANKS
BUT YOU FORGETTEN THAT I NEED LET SAY 10 COLUMNS VALUE MUST BE > 120 KG.
cAN I DO LIKE THIS.
SELECT column1,column2,column3,.... column10 FROM
( SELECT column1,column2,column3,.... column10 FROM Table_Name
ORDER BY dbms_random.value )
WHERE rownum = 120;
 

Nipigie

Senior Member
Joined
Nov 2, 2010
Messages
121
Likes
2
Points
0

Nipigie

Senior Member
Joined Nov 2, 2010
121 2 0
with ten columns you can go with

SELECT column1,column2,column3,.... column10 FROM
( SELECT column1,column2,column3,.... column10 FROM Table_Name
ORDER BY dbms_random.value )
WHERE rownum = 1;
THANKS
BUT YOU FORGETTEN THAT I NEED LET SAY 10 COLUMNS VALUE MUST BE > 120 KG.
cAN I DO LIKE THIS.
SELECT column1,column2,column3,.... column10 FROM
( SELECT column1,column2,column3,.... column10 FROM Table_Name
ORDER BY dbms_random.value )
WHERE rownum = 120;
 

The Spit

JF-Expert Member
Joined
Feb 3, 2009
Messages
418
Likes
49
Points
45

The Spit

JF-Expert Member
Joined Feb 3, 2009
418 49 45
THANKS
BUT YOU FORGETTEN THAT I NEED LET SAY 10 COLUMNS VALUE MUST BE > 120 KG.
cAN I DO LIKE THIS.
SELECT column1,column2,column3,.... column10 FROM
( SELECT column1,column2,column3,.... column10 FROM Table_Name
ORDER BY dbms_random.value )
WHERE rownum = 120;
no use this here and i think it will suite your need;

SELECT column1,column2,column3,.... column10 FROM
( SELECT column1,column2,column3,.... column10 FROM Table_Name
ORDER BY dbms_random.value )
WHERE rownum <= 120;

with row limiting you should use (<=)expression, (>=)will likely work with zero and first positive integer i.e (>=0 or >=1 but not >=5 or >=120)
 

Forum statistics

Threads 1,203,884
Members 457,010
Posts 28,133,346