If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > select n random rows from a table

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,398
select tabname from syscat.tables order by rand() fetch first 5 rows only
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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.
Must Read before posting
__________________
--
Lennart
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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 14:53.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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?
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On