Posted by Jim Michaels on 01/10/06 01:40

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]