How to Select a random row from a database table

[Nipigie]

Hallo, is any one knows sql code for selecting a random row from database table (oracle).
Thanks

 

[The Spit]

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]

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]

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]

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]

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]

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]

Jakathesilease advise me on best materials to become a oracle expert!thanks in advance

 

[BONGOLALA]

Jakathesilease advise me on best materials to become a oracle expert!thnx in advance