How to Select a random row from a database table


Nipigie

Nipigie

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

Nipigie

Senior Member
Joined Nov 2, 2010
121 2 0
Hallo, is any one knows sql code for selecting a random row from database table (oracle).
Thanks
 
The Spit

The Spit

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

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

Nipigie

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

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

The Spit

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

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

Nipigie

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

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

Nipigie

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

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

The Spit

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

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)
 
BONGOLALA

BONGOLALA

JF-Expert Member
Joined
Sep 14, 2009
Messages
14,909
Likes
4,388
Points
280
BONGOLALA

BONGOLALA

JF-Expert Member
Joined Sep 14, 2009
14,909 4,388 280
Jakathesi:please advise me on best materials to become a oracle expert!thanks in advance
 
BONGOLALA

BONGOLALA

JF-Expert Member
Joined
Sep 14, 2009
Messages
14,909
Likes
4,388
Points
280
BONGOLALA

BONGOLALA

JF-Expert Member
Joined Sep 14, 2009
14,909 4,388 280
Jakathesi:please advise me on best materials to become a oracle expert!thnx in advance
 

Forum statistics

Threads 1,236,219
Members 475,029
Posts 29,250,263