How to Select a random row from a database table

Nipigie

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

The Spit

JF-Expert Member
Feb 3, 2009
452
250
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
Nov 2, 2010
121
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
Feb 3, 2009
452
250
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
Nov 2, 2010
121
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
Nov 2, 2010
121
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
Feb 3, 2009
452
250
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

JF-Expert Member
Sep 14, 2009
16,530
2,000
Jakathesi:please advise me on best materials to become a oracle expert!thanks in advance
 

BONGOLALA

JF-Expert Member
Sep 14, 2009
16,530
2,000
Jakathesi:please advise me on best materials to become a oracle expert!thnx in advance
 

Toa taarifa ya maudhui yasiyofaa!

Kuna taarifa umeiona humu JamiiForums na haifai kubaki mtandaoni?
Fanya hivi...

Umesahau Password au akaunti yako?

Unapata ugumu kuikumbuka akaunti yako? Unakwama kuanzisha akaunti?
Contact us

Similar Discussions

Top Bottom