* David V. Edelstein > I would like to create a mySQL query that will find the max > user_id, use this value to calculate a random value of the > user_id from [1 thru max], then return the entire row for > that person(user_id) filled in with that persons(row) four > entities, which I can use to populate the people object.
Because of the lack of sub-selects in mysql, I suggested using user variables: > select @max:=max(user_id) from people; > select round((@max-1)*rand()+1) from people limit 1; > > Returns: 1 field, 1 record which is empty. You should get a random number. I did not notice you wanted the entire people record, sorry. Disregard the above select-statements anyway, they are no good. :/ > select round((max(user_id)-1)*rand()+1) from people limit 1; > > returns: 1 field, 1 record populated with a random number between > 1 and max of the user_id column. > This is great, but how can I put this together, so that the query will > return 4 fields, 1 record that is populated with the 4 entities > for a single random person? Yes, you wanted * from people... try this: select @rnd:=round((max(user_id)-1)*rand()+1) from people; select * from people where user_id = @rnd; If some user_id's are missing you can use "user_id >= @rnd limit 1" to get the first user_id after the missing. > Thanks a bunch for your help! You're welcome! :) > ps: When you state @max: does this create a variable @max in a > mysql buffer that you can use in subsequent queries? Yes, the variable is saved in the server memory. > If so when does this variable > expire? When you close the connection? yes. :) <URL: http://www.mysql.com/doc/V/a/Variables.html > <URL: http://www.mysql.com/doc/e/x/example-user-variables.html > (This last example is similar to your problem.) -- Roger --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
