retrieving random values

[gomzi]

hi,
Suppose i have a table say football,
then depending on the country i have some clubs,
i.e. england - manu,chelsea,arsenal,...
spain - barca,real,zaragova...
italy - ac milan,inter, juventus...

now if i want to select some clubs randomly i.e. say two random clubs from each country, then how should i go about it?

[code green]

then depending on the country i have some clubs,

This is meaningless. What is your table structure

[gomzi]

This is meaningless. What is your table structure

Just forget the whole thing. I would like to know whether its possible to pick some random rows from a table.

i.e. table_A has 10 records. i would like 2 records to be picked from the table at random every time a select is made on the table.

[code green]

Please do not give up. You have a legitimate and interesting problem. It is just that you did not provide enough information to work on.
Try this

SELECT * FROM `table_a`
         ORDER BY RAND() LIMIT 2

or

SELECT * FROM `table_a`
          WHERE `field`='somevalue'
          ORDER BY RAND() LIMIT 2

[gomzi]

Please do not give up. You have a legitimate and interesting problem. It is just that you did not provide enough information to work on.
Try this

SELECT * FROM `table_a`
         ORDER BY RAND() LIMIT 2

or

SELECT * FROM `table_a`
          WHERE `field`='somevalue'
          ORDER BY RAND() LIMIT 2

Thanks a lot mate. Its working perfectly. But for retrieving 2 values i have to say ,
order by rand() limit 3 i.e. one more than that required. any particular reasons for that?

[code green]

But for retrieving 2 values i have to say ,
order by rand() limit 3 i.e. one more than that required

Sorry, I don't understand your question.

[gomzi]

Sorry, I don't understand your question.

when i specify this query,
SELECT * FROM `table_a`
WHERE `field`='somevalue'
ORDER BY RAND() LIMIT 2
it selects a single row

and when i provide this,
SELECT * FROM `table_a`
WHERE `field`='somevalue'
ORDER BY RAND() LIMIT 3

it selects two rows.

that is , depending on the limit it selects the number of rows .

but the limit has to be one more than that required.cant understand as to why its that way.

[code green]

I can only suggest that your WHERE condition is filtering out the other record

[gomzi]

I can only suggest that your WHERE condition is filtering out the other record

I dont think that the problem is with the where condition because, when i increase the limit i get more values, which means that there are many values that are returned based on that where condition.

anyway, its fine ...
thanks a lot mate....m getting the random values which i needed. so no probs as of now!!