Get RAND() results WITH one required row?

[cbmtrx]

I have a basic select statement:

 

SELECT id AS prodId FROM products WHERE (title='kookaburra') ORDER BY RAND() LIMIT 0, 8;

 

from a table containing id,title,description. But of those 8 random results I need to require one record be included IF it's title='emu'.

 

Any way to do this with one SELECT statement or a fancy ORDER BY of some kind? I also tried a subselect but it threw an error... Seems like this should be straightforward enough but I'm a bit stumped.

 

Ideally, I'd want 7 random results + the required one (if it exists), otherwise 8 random results.

 

Any advice?

 

(mysql version 4.1)

[Pikachu2000]

Google 'why order by rand is bad'.

[cbmtrx]

Yes, on huge tables with hundreds of records, which is not an issue here...which is why I'm using it.

 

But do you have a suggestion on the original question?

[fenway]

Then get 8 random ones, union in the one you really want, and then throw out one of the original 8 if necessary.

[cbmtrx]

ahh....UNION. Never used this much and will investigate! tx

[cbmtrx]

OK, it seemed a little finicky but it totally works. To get 7 random items + 1 specific item:

 

(SELECT id FROM products WHERE (title != 'something specific' AND category='marmalades') ORDER BY RAND() LIMIT 0, 7)
        
        UNION
        
        (SELECT id FROM products WHERE (title='something specific' AND category='marmalades') LIMIT 0, 1);

 

I ended up having to force title != 'something specific' in the first SELECT and then force title = 'something specific' in the second one to achieve the desired result.

 

Unfortunately this doesn't yet allow for getting only 8 random results if no 'something specific' exists but it's a workable solution.

 

Thanks!