Logical1

I am trying to pick up a random record (column ColName) form a MySQL table (tblExample).
All references that I have checked say that use ORDER BY RAND():

select ColName from tblExample ORDER by RAND()
Which I have been doing. But instead of 1 record I am getting the entire set of records. If the table has 20 records I am getting all the 20!

I have tried adding Limit 0,1 in the end but I keep on getting error messages as soon as LIMIT is entered into the select query.


Any idea as what is the problem?
Thanks in advance
L1

__________________
^c^

Arla

Did you try select top(1) from tblexample order by rand()

That seems to be what I've found, although everything I've found has also said that using order by Rand() is horrific in performance terms.

Logical1

No I am simply trying to pick a random record and RAND() altough is s slow process should do the job.
May be I should change the questions:

I need to get a random record from a table of 2000 records. What is the best way to do this.
Table is structured like this:

ID Col1 Col2 Col3 Category
1 Joe Jane Jean Part time
2 Mike Ted Rose Full time
3 Ahmed Hans Mina Part time

I would like to show a random part time Id. Both Category and Id are factors in selection so I can not first generate a random number and open that number for Id.

Any suggestion is apreciated
L1

__________________
^c^

Apophis

i think the query should be changed to: select ColName from tblExample where ID = [Random Number]

grimpirate

Retrieve the entries you want into an array and then use the array_rand function provided by php.

__________________
Lo, there do I see my father. 'Lo, there do I see My mother, and my sisters, and my brothers. 'Lo, there do I see The line of my people... Back to the beginning. 'Lo, they do call to me. They bid me take my place among them. In the halls of Valhalla... Where the brave... May live... ...forever.. GrimBB | Mimesis

Logical1

Thanks for suggestions guys.
Grimpirate
I did some tests including using a much larger table and trying to see how often Rand will repeat.
Results are appaling. 2 out of 10 times the very first record is repeated and one can see a patern of repeated numbers.
Is RAND like this or am I doing something wrong?

Apopis that won't work I need to use the category also.

__________________
^c^

Sane

Are you following the documentation?

See: array_rand()

Make sure you seed the random number generator with srand(). Also, specify how many random elements you need to pull from the array in the function's optional parameter (instead of making the naive mistake of pulling them one by one with array_rand($input)).

Follow this example code, and replace the $input assignment with the result of your database lookup.

__________________
Waterloo's Canadian Computing Competition (CCC) - Stage 2 Problems, Solutions, and Test Data