Easier method: SELECT * FROM people ORDER BY RAND() LIMIT 1;
-----Original Message----- From: David V. Edelstein [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 09, 2002 1:08 PM To: 'Roger Baklund' Cc: '[EMAIL PROTECTED]' Subject: Random Select Statement - help please Hi Roger, Thanks for your reply and I apologize for my lack of specificity on my previous email, thanks for your patience :). I have a people object that has 4 entities. name, pword, user_id, picurl This data is stored in the people table. Each user is identified by a unique user_id. 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. 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. 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? Thanks a bunch for your help! Best regards, David ps: When you state @max: does this create a variable @max in a mysql buffer that you can use in subsequent queries? If so when does this variable expire? When you close the connection? -----Original Message----- From: Roger Baklund [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 09, 2002 2:40 AM To: David V. Edelstein Subject: RE: Random Select Statement?? > Sorry Roger, > It's not working correctly. > Please, how can I accomplish the following? > > select * from people where user_id=(get the random #) limit 1; It is difficult to help you when you don't say what the problem is... do you get an error message? Do you get the wrong result? No result? These are valid sql statements, but they may need mysql version 3.23 or later, and both statements must be run within the same session (I don't think you can use phpadmin or similar, unless multiple commands can be executed at the same time): select @max:=max(user_id) from people; select round((@max-1)*rand()+1) from people limit 1; Your own statement should also work: select round((max(user_id)-1)*rand()+1) from people limit 1; If it doesn't work, something is wrong... ;) - You must enter the statements in the mysql client - You must be connected to the correct database - You must have select privileges on the table - The table must be named 'people', and the field must be named 'user_id' -- 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 --------------------------------------------------------------------- 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
