[RESOLVED] Random rows from db

zzz

I have a table with some data. I run a look and print out the results. Currently I limit the output to 20. Is it possible to get random 20 results?

SELECT * 
FROM ...
WHERE ...
LIMIT 20

:rolleyes:

NogDog

SELECT *, RAND() AS rand_sort FROM TABLE ORDER BY rand_sort LIMIT 20;

(Verify what the correct random function/syntax is for your DBMS.)

zzz

Uh, I'm gonna give it whirl right away, thanks.

Since my last post I have thought about one more think to complicate my life.

My table has similar to this structure:

itemID
projectID
itemName

Innitially I was planning to grab 20 randomly. Now, since some items belong to the same project and have the same projectID, how can I grab random 20 but only one per unique projectID?

😕

bradgrafelman

Try adding a 'GROUP BY projectID' onto the end of your query.

zzz

Found myself a bit lost.

Normally I use a class to run query:

$longQuery = $db->get_results ("SELECT itemName FROM t1 WHERE status= 'active' LIMIT 10");

foreach ($longQueryas $lq) {

echo $lq->itemName.'<br>';

}

Now, adding RAND() AS rand_sort sort of throws me off. How would I implement it. Shouldn't it be something like: SELECT RAND(*)...? I could not find RAND() in mysql functions list... When I tried what I suggested above my query just did not work.

zzz

Got it!!!

ORDER BY RAND(projectID)