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:
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?