Random distinct or group by or perhaps order by?

Databases
1

Back again with another problem query… :slight_smile:

I have a table called urls
in this table i have 4 fields:
url_id (primary key)
url_url - (unique)
url_ module
url_title

the url_module field will be one of 5 values:
forum
products
links
blog
gallery

what im trying to do is select a random row for each url_module type so that the result will consist of 1 random forum url, 1 random products url, 1 random links url, 1 random blogs url and 1 random gallery url…

does that make sense? and is it possible to achieve this in one query using group by or order by or distinct or …

I would appreciate any help or pointing in the right direction

2

here’s one method –

( SELECT url_url, url_module, url_title
    FROM urls
   WHERE url_module = 'forum'
  ORDER BY RAND() LIMIT 1 )
UNION ALL
( SELECT url_url, url_module, url_title
    FROM urls
   WHERE url_module = 'products'
  ORDER BY RAND() LIMIT 1 )
UNION ALL
.
.
.

this is 5 times as slow, however, as pulling say a dozen random rows and looping through them to find the 5 different module types

(yes, there is a chance that you won’t find all 5, but that’s a small chance, and all you have to do is loop back and get a dozen more when that happens)