Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Unanswered: Retrieval of Random rows

    Setup: 9iDB

    Is there any way to say "give me 1000 rows randomly selected from the table" in a SQL statement? The sample directive will not do it, is this something I need to program myself?
    NOTE: Please disregard the label "Senior Member".

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    something like that?
    select ... from table where rownum < 1001 ?

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Try:
    Code:
    select ... from 
    (select ... from table order by dbms_random.value)
    where rownum < 1001;

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    from
    http://download-west.oracle.com/docs...3a.htm#2065954


    sample_clause

    The sample_clause lets you instruct Oracle to select from a random sample of rows from the table, rather than from the entire table.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Thumbs up

    Thanks for all the responses.

    I had thought SAMPLE with rownum < 1001, should have worked, but the results do not appear to be truly random when combined with the rownum clause.

    Tony's suggestion seems to work like I need.
    NOTE: Please disregard the label "Senior Member".

  6. #6
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    It's just me or nobody thought about this solution:

    Code:
    select 'give me 1000 rows randomly selected from the table'
      from dual;
    Sorry, I just couldn't avoid the joke... It's a nice one if you consider I'm Argentinian...

    Thanks for the tip, I didn't know that package, Tony!

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> select count(*) from DW_2000000010566.eorder;
    
      COUNT(*)
    ----------
        788181
    
    SQL> select count(*) from DW_2000000010566.eorder sample(1);
    
      COUNT(*)
    ----------
          7921
    
    SQL> /
    
      COUNT(*)
    ----------
          7896
    
    SQL> /
    
      COUNT(*)
    ----------
          7835
    
    SQL> /
    
      COUNT(*)
    ----------
          7764
    It seems to work as expected for me.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •