select n random rows from a table

arronlacey · #1 (**permalink**) 04-20-12, 10:05

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

n_i · #2 (**permalink**) 04-20-12, 10:24

select tabname from syscat.tables order by rand() fetch first 5 rows only

lelle12 · #3 (**permalink**) 04-20-12, 10:26

Quote:

Originally Posted by arronlacey

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

arronlacey · #4 (**permalink**) 04-20-12, 10:34

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

The order by clause in the following position is invalid.

lelle12 · #5 (**permalink**) 04-20-12, 11:27

Quote:

Originally Posted by arronlacey

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

The order by clause in the following position is invalid.

Must Read before posting

Lenny77 · #6 (**permalink**) 04-20-12, 12:50

Quote:

Originally Posted by arronlacey

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

stolze · #7 (**permalink**) 04-27-12, 15:08

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. ;-)

makr8100 · #8 (**permalink**) 01-18-13, 15:19

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?

Internet Services

Web Development

Digital Marketing

Consumer Tech