Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2012
    Posts
    2

    select n random rows from a table

    I need to take a subset of n rows at random from a dataset. How do I do this? Fetch First n rows always brings back the same rows so i do not need this. Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,446
    select tabname from syscat.tables order by rand() fetch first 5 rows only

  3. #3
    Join Date
    Mar 2003
    Posts
    279
    Quote Originally Posted by arronlacey View Post
    I need to take a subset of n rows at random from a dataset. How do I do this? Fetch First n rows always brings back the same rows so i do not need this. Thanks
    select ...
    from T
    order by rand()
    fetch first 10 rows only

    another option

    select ...
    from T
    tablesample system(1)
    fetch first 10 rows only
    --
    Lennart

  4. #4
    Join Date
    Apr 2012
    Posts
    2

    hi

    Thanks for your reply - using your first method I get :

    The order by clause in the following position is invalid.

  5. #5
    Join Date
    Mar 2003
    Posts
    279
    Quote Originally Posted by arronlacey View Post
    Thanks for your reply - using your first method I get :

    The order by clause in the following position is invalid.
    http://www.dbforums.com/db2/854783-m...e-posting.html
    --
    Lennart

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up

    Quote Originally Posted by arronlacey View Post
    I need to take a subset of n rows at random from a dataset. How do I do this? Fetch First n rows always brings back the same rows so i do not need this. Thanks
    This query will work:

    Code:
    select tt.* from
    (select rand() rnd, your.* from your_table your) tt
    order by 1 
    Fetch first N rows only
    Lenny
    Last edited by Lenny77; 04-20-12 at 15:53.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Sticking strictly to SQL semantics:
    Code:
    SELECT *
    FROM your_table
    FETCH FIRST 5 ROWS ONLY
    You will get (up to) 5 arbitrary rows, at least DB2 is free to give you any 5 rows from the table because a table is a set of rows and sets are - per definition - not sorted. So there is no sensible notion of "first 5".

    But I guess this is not really what you want. ;-)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Jan 2013
    Location
    central USA
    Posts
    1
    I'm trying to do something similar for a website I'm working with. The box I'm working on is running db2 on i5 OS. I'm not sure about version numbers as our customers have varied setups. While working on a shopping cart website I'm using this statement:

    Code:
    SELECT i.line, i.item FROM cecart.itemweb i JOIN cecart.itemcatg c
    ON i.line=c.icline AND i.item=c.icitem
    WHERE c.iccatid IN(SELECT iccatid FROM cecart.itemcatg WHERE icline='$line' AND icitem='$item')
    AND CONCAT(TRIM(i.line), TRIM(i.item))<>CONCAT('$line', '$item')
    ORDER BY i.featitm DESC, RAND() FETCH FIRST $limit ROWS ONLY
    In this statement $line + $item form the PK, and $limit of course is a variable passed to the PHP function this statement is in. When RAND() is not in the ORDER clause this statement works as expected, but when RAND() is added I also get a random number of results. If there are more than 4 results I should always get 4, but adding the RAND() in the ORDER clause seems to also randomize the number of results between 1 and 4. I tried moving the RAND() to the SELECT clause, naming it with AS, and ordering by that, and the results were the same. Any guesses why RAND() in ORDER BY throws this statement off?

Posting Permissions

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