Help with displaying random records with MySQL

[DanCorb]

I have the following table:

ID	Job	Category
1	Clerk	Admin
2	Secretary	Admin
3	Reception	Admin
4	Typist	Admin

Now, I want to display 5 jobs within the Admin Category, but I want them pulled randomly from the database. ORDER BY RAND won't work because there are only 4 records. I need to display 5 records as it doesn't matter if the same record is displayed more than once. How do I do this?

[NogDog]

I'd probably just loop through the result set as usual, but keep a counter of records displayed. Then if the counter is less than 5, reset the result pointer back to the first record, and start cycling through it again until the counter reaches 5. (Of course, check for 0 results first, so you don't get into an infinite loop.)

[gk53]

try something like that

select * from
(SELECT ID, 	Job, 	Category
FROM employee
union all
SELECT ID, 	Job, 	Category
FROM employee)
ORDER BY RAND()
LIMIT 5