Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    50

    Unanswered: Getting a random line

    Hi!

    How can I get a random line from a table? I just want one and I want it different in successives queries to the database.

    Thank you for your attention.
    Ah! Não ser eu toda a gente e toda a parte!

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    This will get one row......

    select * from mytable sample(1) where rownum <= 1

    bear in mind the SAMPLE() parameter is percent of total rows. You will have to bear this in mind for very large or very small tables and you can increase or decrease the sample size accordingly.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Dec 2003
    Posts
    50
    Yes, but the problem using sample for a small table is that the result is almost always the same, whick makes me think that it really doesn't randomize the lines. Isn't there another way?
    Ah! Não ser eu toda a gente e toda a parte!

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    select * from
    (
    select * from emp
    order by dbms_random.value
    ) where rownum = 1;

  5. #5
    Join Date
    Dec 2003
    Posts
    50
    Thanks a lot. The result is exactly what I pretended.
    Ah! Não ser eu toda a gente e toda a parte!

Posting Permissions

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