|
|
Posted by Jim Michaels on 01/10/06 02:11
OOPS! I was wrong. you *can* use RAND() in an ORDER BY clause. the manual
states:
You can't use a column with RAND() values in an ORDER BY clause, because
ORDER BY would evaluate the column multiple times. As of MySQL 3.23, you can
retrieve rows in random order like this:
mysql> SELECT * FROM tbl_name ORDER BY RAND();
ORDER BY RAND() combined with LIMIT is useful for selecting a random sample
of a set of rows:
mysql> SELECT * FROM table1, table2 WHERE a=b AND c<d
-> ORDER BY RAND() LIMIT 1000;
Note that RAND() in a WHERE clause is re-evaluated every time the WHERE is
executed.
"Jim Michaels" <jmichae3@yahoo.com> wrote in message
news:Lr6dnbzf_84baV_eRVn-gw@comcast.com...
> try looking in the MySQL manual for the CASE statement.
> I think it might fit your needs for 3 rows. but if you have more than
> that, you've got a different problem to solve.
> MySQL also has a RAND() function which generates a FLOAT number between
> 0.0 and 1.0. you can't use it in an ORDER BY clause, because RAND() would
> recalc multiple times. might be better to stuff it in a @VARIABLE first
> if you can, like @V:=RAND();
> ahh - maybe it's simpler doing it in PHP anyway. :-)
>
> SELECT CASE WHERE condition THEN column WHERE condition THEN column ELSE
> column END FROM tablename;
> I think that's MySQL's version of CASE. SQL2 doesn't use END.
>
> "Jonathan" <jonathan@heelal.nl> wrote in message
> news:43c0d064$0$10081$ba620dc5@text.nova.planet.nl...
>> Joe wrote:
>>> "Stefan Rybacki" <stefan.rybacki@gmx.net> skrev i en meddelelse
>>> news:42apniF1hejs7U1@individual.net...
>>>
>>>>-----BEGIN PGP SIGNED MESSAGE-----
>>>>Hash: SHA1
>>>>
>>>>Joe wrote:
>>>>
>>>>>Hi
>>>>>I need some help doing a easy php/sql solution to a random selection
>>>>>with
>>>>>rated stuff.
>>>>>I have a table like:
>>>>>
>>>>>Name Rated
>>>>>--------------
>>>>>Thompsen 500
>>>>>Milla 10
>>>>>Jensen 1
>>>>>
>>>>>Means that the name Thompsen appears 50 times as often as Milla and 500
>>>>>times as often Jensen.
>>>>>
>>>>>I now need some scripting that counts the total ratefactor like
>>>>>SELECT sum(Rated) FROM Table
>>>>>In this case 511
>>>>>And then make a random value between 1 and 511 and then print out the
>>>>>name,
>>>>>which in this case will be Thompsen 500 of 511 times (in avg)
>>>>>
>>>>>I need a quick way to find a name based on the Rated factors, any help?
>>>>>
>>>>
>>>>1. get your ratefactor (SELECT sum(rated) FROM table)
>>>>2. create a random number between 1 and ratefactor in PHP
>>>>3. get the random item (SELECT name FROM table WHERE
>>>>rated>=created_randomnumber
>>>>ORDER BY rated, rand() LIMIT 1)
>>>>
>>>>Regards
>>>>Stefan
>>>>
>>>
>>>
>>> I think I need some explanation for this :-)
>>>
>>> As I see it it will give equal chance of the three names if the random
>>> number is 1, is that correct?
>>>
>>> If that's correct it will not be 1/511 chance of the name Jensen to
>>> appear.
>>>
>>
>> This might be a case of simple misunderstanding at first I thought you
>> had a a table with 500 rows containing the name Thompsen, 10 rows
>> containing the name Milla and one row containing the name Milla. This
>> makes a total of 511 rows. Then selecting based on the rownumber would
>> give you your statistic distribution.
>>
>> But after reading your post another time I think the solution is a bit
>> more complex... as the table has only three rows with each name appearing
>> only once in combination with a number indicating the change on the
>> total. This is a bit harder to solve than if you would really have 511
>> rows containg the data.
>>
>> I think you need to use some program logic (or a stored procedure) to
>> determine which name to display:
>>
>> 1. Make a table that sums the rated number for every row above including
>> it self e.g.:
>>
>> Name Rated_Sum
>> ----------------------
>> Thompson 500
>> Milla 510
>> Jensen 511
>>
>> 2. Generate a random number with the range from 1 to the maximum value
>> (the value in the last row of the table) to determin something like the
>> row index in the example
>>
>> 3. Use logic to see if the random number is smaller or equal to the
>> Rated_Sum value of its own value and greater than the value in the last
>> row before its own value, e.g.:
>>
>> The random number would be in the range of 1 up to and including 500 then
>> you should return Thompson, but if the random number is greater than 510
>> and less than or equal to 511 (only 511 would fit this condition,
>> assuming that you generate integer random numbers) you have to output
>> Jensen.
>>
>> It makes it all a bit complicated... it might be easier to create a
>> temporary table holding the number of rows with the rated value and go
>> with the example of Stephan.
>>
>> Good luck!
>>
>> Jonathan
>
>
[Back to original message]
|