Dismiss Notice
You are browsing this site as a guest. It takes 2 minutes to CREATE AN ACCOUNT and less than 1 minute to LOGIN

How to Select a random row from a database table

Discussion in 'Tech, Gadgets & Science Forum' started by Nipigie, Nov 17, 2010.

  1. Nipigie

    Nipigie Senior Member

    #1
    Nov 17, 2010
    Joined: Nov 2, 2010
    Messages: 121
    Likes Received: 2
    Trophy Points: 0
    Hallo, is any one knows sql code for selecting a random row from database table (oracle).
    Thanks
     
  2. The Spit

    The Spit JF-Expert Member

    #2
    Nov 17, 2010
    Joined: Feb 3, 2009
    Messages: 374
    Likes Received: 14
    Trophy Points: 35
    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.
     
  3. Nipigie

    Nipigie Senior Member

    #3
    Nov 17, 2010
    Joined: Nov 2, 2010
    Messages: 121
    Likes Received: 2
    Trophy Points: 0
    Thanks jakathesi, it works, but i want now to select 10 columns randomly but must be superior to certain value. your help please.
     
  4. The Spit

    The Spit JF-Expert Member

    #4
    Nov 17, 2010
    Joined: Feb 3, 2009
    Messages: 374
    Likes Received: 14
    Trophy Points: 35
    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;
     
  5. Nipigie

    Nipigie Senior Member

    #5
    Nov 17, 2010
    Joined: Nov 2, 2010
    Messages: 121
    Likes Received: 2
    Trophy Points: 0
    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;
     
  6. Nipigie

    Nipigie Senior Member

    #6
    Nov 17, 2010
    Joined: Nov 2, 2010
    Messages: 121
    Likes Received: 2
    Trophy Points: 0
    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;
     
  7. The Spit

    The Spit JF-Expert Member

    #7
    Nov 17, 2010
    Joined: Feb 3, 2009
    Messages: 374
    Likes Received: 14
    Trophy Points: 35
    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)
     
  8. BONGOLALA

    BONGOLALA JF-Expert Member

    #8
    Nov 17, 2010
    Joined: Sep 14, 2009
    Messages: 13,040
    Likes Received: 1,339
    Trophy Points: 280
    Jakathesi:please advise me on best materials to become a oracle expert!thanks in advance
     
  9. BONGOLALA

    BONGOLALA JF-Expert Member

    #9
    Nov 17, 2010
    Joined: Sep 14, 2009
    Messages: 13,040
    Likes Received: 1,339
    Trophy Points: 280
    Jakathesi:please advise me on best materials to become a oracle expert!thnx in advance
     
Loading...