Using RAND to get a unique ID that has not been used yet
Hi All, I have a table that uses auto_increment to generate the Id automatically working fine. However, I need to create a new table where the Id must be a number generated randomly, so I cannot use the auto_increment. MySQL has a function RAND. So I could use something like this: SELECT FLOOR(RAND() * COUNT(*)) AS RandId FROM mytable But, let's suppose that the RandId is a number that was already used in the table. Then I need to run the SELECT again and again until I find a number that hasn't been used. Is there a way to have this SELECT to loop until it finds a number that hasn't been used? The RandId must be only numbers and length of 6 (from 1 to 99). No other character is allowed. Thanks for any help! Andre -- Andre Matos andrema...@mineirinho.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Using RAND to get a unique ID that has not been used yet
If your specs are that specific (IDs must be between 1 and 99) then you could create a 99-row table with one integer column and prefill it with the numbers 1 to 99 in random order. Then you could write a function that would select and return the first number in the table, then delete that record so you would not reuse it. Once you've done the work of sorting 99 numbers in random order (which can be done anywhich way) it's easy and you don't have to loop an indeterminant number of times. You would be looping an increasing number of times as you begin to fill up the table. Jim On Fri, May 28, 2010 at 10:38 AM, Andre Matos andrema...@mineirinho.org wrote: Hi All, I have a table that uses auto_increment to generate the Id automatically working fine. However, I need to create a new table where the Id must be a number generated randomly, so I cannot use the auto_increment. MySQL has a function RAND. So I could use something like this: SELECT FLOOR(RAND() * COUNT(*)) AS RandId FROM mytable But, let's suppose that the RandId is a number that was already used in the table. Then I need to run the SELECT again and again until I find a number that hasn't been used. Is there a way to have this SELECT to loop until it finds a number that hasn't been used? The RandId must be only numbers and length of 6 (from 1 to 99). No other character is allowed. Thanks for any help! Andre -- Andre Matos andrema...@mineirinho.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Using RAND to get a unique ID that has not been used yet
If you wanted to use/go that route, then why not select a random limit 1 from that table, and then delete that row? SELECT `column` FROM `table` ORDER BY RAND() LIMIT 1; On a side note, I would use the auto-inc field still, and store this number in another field. Steven Staples -Original Message- From: Jim Lyons [mailto:jlyons4...@gmail.com] Sent: May 28, 2010 11:49 AM To: Andre Matos Cc: mysql@lists.mysql.com Subject: Re: Using RAND to get a unique ID that has not been used yet If your specs are that specific (IDs must be between 1 and 99) then you could create a 99-row table with one integer column and prefill it with the numbers 1 to 99 in random order. Then you could write a function that would select and return the first number in the table, then delete that record so you would not reuse it. Once you've done the work of sorting 99 numbers in random order (which can be done anywhich way) it's easy and you don't have to loop an indeterminant number of times. You would be looping an increasing number of times as you begin to fill up the table. Jim On Fri, May 28, 2010 at 10:38 AM, Andre Matos andrema...@mineirinho.org wrote: Hi All, I have a table that uses auto_increment to generate the Id automatically working fine. However, I need to create a new table where the Id must be a number generated randomly, so I cannot use the auto_increment. MySQL has a function RAND. So I could use something like this: SELECT FLOOR(RAND() * COUNT(*)) AS RandId FROM mytable But, let's suppose that the RandId is a number that was already used in the table. Then I need to run the SELECT again and again until I find a number that hasn't been used. Is there a way to have this SELECT to loop until it finds a number that hasn't been used? The RandId must be only numbers and length of 6 (from 1 to 99). No other character is allowed. Thanks for any help! Andre -- Andre Matos andrema...@mineirinho.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.819 / Virus Database: 271.1.1/2895 - Release Date: 05/28/10 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Using RAND to get a unique ID that has not been used yet
It seems to be a good approach, although I was trying to get this by querying the table without creating another table to keep the Ids. Thanks, Andre -- Andre Matos andrema...@mineirinho.org On 2010-05-28, at 12:15 PM, Steven Staples wrote: If you wanted to use/go that route, then why not select a random limit 1 from that table, and then delete that row? SELECT `column` FROM `table` ORDER BY RAND() LIMIT 1; On a side note, I would use the auto-inc field still, and store this number in another field. Steven Staples -Original Message- From: Jim Lyons [mailto:jlyons4...@gmail.com] Sent: May 28, 2010 11:49 AM To: Andre Matos Cc: mysql@lists.mysql.com Subject: Re: Using RAND to get a unique ID that has not been used yet If your specs are that specific (IDs must be between 1 and 99) then you could create a 99-row table with one integer column and prefill it with the numbers 1 to 99 in random order. Then you could write a function that would select and return the first number in the table, then delete that record so you would not reuse it. Once you've done the work of sorting 99 numbers in random order (which can be done anywhich way) it's easy and you don't have to loop an indeterminant number of times. You would be looping an increasing number of times as you begin to fill up the table. Jim On Fri, May 28, 2010 at 10:38 AM, Andre Matos andrema...@mineirinho.org wrote: Hi All, I have a table that uses auto_increment to generate the Id automatically working fine. However, I need to create a new table where the Id must be a number generated randomly, so I cannot use the auto_increment. MySQL has a function RAND. So I could use something like this: SELECT FLOOR(RAND() * COUNT(*)) AS RandId FROM mytable But, let's suppose that the RandId is a number that was already used in the table. Then I need to run the SELECT again and again until I find a number that hasn't been used. Is there a way to have this SELECT to loop until it finds a number that hasn't been used? The RandId must be only numbers and length of 6 (from 1 to 99). No other character is allowed. Thanks for any help! Andre -- Andre Matos andrema...@mineirinho.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.819 / Virus Database: 271.1.1/2895 - Release Date: 05/28/10 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrema...@mineirinho.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Using RAND to get a unique ID that has not been used yet
-Original Message- From: Jim Lyons [mailto:jlyons4...@gmail.com] Sent: Friday, May 28, 2010 11:49 AM To: Andre Matos Cc: mysql@lists.mysql.com Subject: Re: Using RAND to get a unique ID that has not been used yet If your specs are that specific (IDs must be between 1 and 99) then you could create a 99-row table with one integer column and prefill it with the numbers 1 to 99 in random order. Then you could write a function that would select and return the first number in the table, then delete that record so you would not reuse it. Once you've done the work of sorting 99 numbers in random order (which can be done anywhich way) it's easy and you don't have to loop an indeterminant number of times. You would be looping an increasing number of times as you begin to fill up the table. [JS] You don't have to go to the trouble of sorting the number table in any order, random or anything else. Just select a random record from that table. Since you'll have the number of that record, you can delete it. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Using RAND to get a unique ID that has not been used yet
-Original Message- From: Andre Matos [mailto:andrema...@mineirinho.org] Sent: Friday, May 28, 2010 1:44 PM To: Steven Staples Cc: mysql@lists.mysql.com Subject: Re: Using RAND to get a unique ID that has not been used yet It seems to be a good approach, although I was trying to get this by querying the table without creating another table to keep the Ids. [JS] That would be a VERY bad idea. My predecessor designed our system that way: it would generate a random key, check to see if that key were in use, and either use it or try again. As you would expect, the whole process get slower and slower as we ran out of unique keys. Eventually the whole application became unusable. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Thanks, Andre -- Andre Matos andrema...@mineirinho.org On 2010-05-28, at 12:15 PM, Steven Staples wrote: If you wanted to use/go that route, then why not select a random limit 1 from that table, and then delete that row? SELECT `column` FROM `table` ORDER BY RAND() LIMIT 1; On a side note, I would use the auto-inc field still, and store this number in another field. Steven Staples -Original Message- From: Jim Lyons [mailto:jlyons4...@gmail.com] Sent: May 28, 2010 11:49 AM To: Andre Matos Cc: mysql@lists.mysql.com Subject: Re: Using RAND to get a unique ID that has not been used yet If your specs are that specific (IDs must be between 1 and 99) then you could create a 99-row table with one integer column and prefill it with the numbers 1 to 99 in random order. Then you could write a function that would select and return the first number in the table, then delete that record so you would not reuse it. Once you've done the work of sorting 99 numbers in random order (which can be done anywhich way) it's easy and you don't have to loop an indeterminant number of times. You would be looping an increasing number of times as you begin to fill up the table. Jim On Fri, May 28, 2010 at 10:38 AM, Andre Matos andrema...@mineirinho.org wrote: Hi All, I have a table that uses auto_increment to generate the Id automatically working fine. However, I need to create a new table where the Id must be a number generated randomly, so I cannot use the auto_increment. MySQL has a function RAND. So I could use something like this: SELECT FLOOR(RAND() * COUNT(*)) AS RandId FROM mytable But, let's suppose that the RandId is a number that was already used in the table. Then I need to run the SELECT again and again until I find a number that hasn't been used. Is there a way to have this SELECT to loop until it finds a number that hasn't been used? The RandId must be only numbers and length of 6 (from 1 to 99). No other character is allowed. Thanks for any help! Andre -- Andre Matos andrema...@mineirinho.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.819 / Virus Database: 271.1.1/2895 - Release Date: 05/28/10 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrema...@mineirinho.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Using RAND to get a unique ID that has not been used yet
When I mentioned having everything in the Query, I was thinking about this. I don't want to have a loop repeating the query until I get a unique Id. This is ridicules and imagine how many queries I might end up running. No way! Thanks for the warning and feedback! Andre -- Andre Matos andrema...@mineirinho.org On 2010-05-28, at 1:51 PM, Jerry Schwartz wrote: -Original Message- From: Andre Matos [mailto:andrema...@mineirinho.org] Sent: Friday, May 28, 2010 1:44 PM To: Steven Staples Cc: mysql@lists.mysql.com Subject: Re: Using RAND to get a unique ID that has not been used yet It seems to be a good approach, although I was trying to get this by querying the table without creating another table to keep the Ids. [JS] That would be a VERY bad idea. My predecessor designed our system that way: it would generate a random key, check to see if that key were in use, and either use it or try again. As you would expect, the whole process get slower and slower as we ran out of unique keys. Eventually the whole application became unusable. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Thanks, Andre -- Andre Matos andrema...@mineirinho.org On 2010-05-28, at 12:15 PM, Steven Staples wrote: If you wanted to use/go that route, then why not select a random limit 1 from that table, and then delete that row? SELECT `column` FROM `table` ORDER BY RAND() LIMIT 1; On a side note, I would use the auto-inc field still, and store this number in another field. Steven Staples -Original Message- From: Jim Lyons [mailto:jlyons4...@gmail.com] Sent: May 28, 2010 11:49 AM To: Andre Matos Cc: mysql@lists.mysql.com Subject: Re: Using RAND to get a unique ID that has not been used yet If your specs are that specific (IDs must be between 1 and 99) then you could create a 99-row table with one integer column and prefill it with the numbers 1 to 99 in random order. Then you could write a function that would select and return the first number in the table, then delete that record so you would not reuse it. Once you've done the work of sorting 99 numbers in random order (which can be done anywhich way) it's easy and you don't have to loop an indeterminant number of times. You would be looping an increasing number of times as you begin to fill up the table. Jim On Fri, May 28, 2010 at 10:38 AM, Andre Matos andrema...@mineirinho.org wrote: Hi All, I have a table that uses auto_increment to generate the Id automatically working fine. However, I need to create a new table where the Id must be a number generated randomly, so I cannot use the auto_increment. MySQL has a function RAND. So I could use something like this: SELECT FLOOR(RAND() * COUNT(*)) AS RandId FROM mytable But, let's suppose that the RandId is a number that was already used in the table. Then I need to run the SELECT again and again until I find a number that hasn't been used. Is there a way to have this SELECT to loop until it finds a number that hasn't been used? The RandId must be only numbers and length of 6 (from 1 to 99). No other character is allowed. Thanks for any help! Andre -- Andre Matos andrema...@mineirinho.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.819 / Virus Database: 271.1.1/2895 - Release Date: 05/28/10 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrema...@mineirinho.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrema...@mineirinho.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Using RAND to get a unique ID that has not been used yet
On Fri, May 28, 2010 at 11:38 AM, Andre Matos andrema...@mineirinho.org wrote: I have a table that uses auto_increment to generate the Id automatically working fine. However, I need to create a new table where the Id must be a number generated randomly, so I cannot use the auto_increment. You'd be better off using UUID in my opinion. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Using RAND to get a unique ID that has not been used yet
The separate table for the IDs is probably best solution, maybe counting on caching of the table with an index on the id value to speed up the 'where' clause; this checks what numbers are left instead of what numbers have been used; the disadvantage is that you have to manage a second table with a million rows! You could generate a memory table when you open the session, populate it with all possible values and then delete all already assigned values. You would have to do this only once and then all possible unused values would be available. It shouldn't get slower with time (in fact it might speed up as the used rows are progressively deleted). It has the advantage that the random function is called only once: whereas using a single table requires looping until a unique random value is found, and as the table fills this will get really slow. - Original Message - From: Jerry Schwartz je...@gii.co.jp To: 'Andre Matos' andrema...@mineirinho.org; 'Steven Staples' sstap...@mnsi.net Cc: mysql@lists.mysql.com Sent: Friday, May 28, 2010 6:51 PM Subject: RE: Using RAND to get a unique ID that has not been used yet -Original Message- From: Andre Matos [mailto:andrema...@mineirinho.org] Sent: Friday, May 28, 2010 1:44 PM To: Steven Staples Cc: mysql@lists.mysql.com Subject: Re: Using RAND to get a unique ID that has not been used yet It seems to be a good approach, although I was trying to get this by querying the table without creating another table to keep the Ids. [JS] That would be a VERY bad idea. My predecessor designed our system that way: it would generate a random key, check to see if that key were in use, and either use it or try again. As you would expect, the whole process get slower and slower as we ran out of unique keys. Eventually the whole application became unusable. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Thanks, Andre -- Andre Matos andrema...@mineirinho.org On 2010-05-28, at 12:15 PM, Steven Staples wrote: If you wanted to use/go that route, then why not select a random limit 1 from that table, and then delete that row? SELECT `column` FROM `table` ORDER BY RAND() LIMIT 1; On a side note, I would use the auto-inc field still, and store this number in another field. Steven Staples -Original Message- From: Jim Lyons [mailto:jlyons4...@gmail.com] Sent: May 28, 2010 11:49 AM To: Andre Matos Cc: mysql@lists.mysql.com Subject: Re: Using RAND to get a unique ID that has not been used yet If your specs are that specific (IDs must be between 1 and 99) then you could create a 99-row table with one integer column and prefill it with the numbers 1 to 99 in random order. Then you could write a function that would select and return the first number in the table, then delete that record so you would not reuse it. Once you've done the work of sorting 99 numbers in random order (which can be done anywhich way) it's easy and you don't have to loop an indeterminant number of times. You would be looping an increasing number of times as you begin to fill up the table. Jim On Fri, May 28, 2010 at 10:38 AM, Andre Matos andrema...@mineirinho.org wrote: Hi All, I have a table that uses auto_increment to generate the Id automatically working fine. However, I need to create a new table where the Id must be a number generated randomly, so I cannot use the auto_increment. MySQL has a function RAND. So I could use something like this: SELECT FLOOR(RAND() * COUNT(*)) AS RandId FROM mytable But, let's suppose that the RandId is a number that was already used in the table. Then I need to run the SELECT again and again until I find a number that hasn't been used. Is there a way to have this SELECT to loop until it finds a number that hasn't been used? The RandId must be only numbers and length of 6 (from 1 to 99). No other character is allowed. Thanks for any help! Andre -- Andre Matos andrema...@mineirinho.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.819 / Virus Database: 271.1.1/2895 - Release Date: 05/28/10 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrema...@mineirinho.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List
Using RAND()
The RAND() syntax does not appear to be working for me. What version mySQL is required? At 04:43 PM 1/29/2002 +0200, Michael Widenius wrote: Hi! Ulf == Ulf Harnhammar [EMAIL PROTECTED] writes: Ulf On Sun, 27 Jan 2002, Michael Widenius wrote: One way to do this is to do as follows: SELECT something FROM sometable WHERE somevar=somevalue ORDER BY RAND() LIMIT 10 - 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
RE: Using RAND()
You have to have MySQL 3.23 or greater.. http://www.mysql.com/doc/M/a/Mathematical_functions.html Gurhan -Original Message- From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 11:33 AM To: [EMAIL PROTECTED]; Ulf Harnhammar Cc: [EMAIL PROTECTED] Subject: Using RAND() The RAND() syntax does not appear to be working for me. What version mySQL is required? At 04:43 PM 1/29/2002 +0200, Michael Widenius wrote: Hi! Ulf == Ulf Harnhammar [EMAIL PROTECTED] writes: Ulf On Sun, 27 Jan 2002, Michael Widenius wrote: One way to do this is to do as follows: SELECT something FROM sometable WHERE somevar=somevalue ORDER BY RAND() LIMIT 10 - 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
Re: Using RAND()
Hi When I used the RAND() function (MySQL 3.23.45) I found the results were not very random, instead I use an extra bit of php code to make sure the results are more random. mt_srand((double)microtime()*100); $sql = select order by RAND(.mt_rand(0,32767).) limit 50 $rs = mysql_query($sql); $num=mysql_num_rows($rs); if($num1) mysql_data_seek($rs,mt_rand(0,$num-1)); $row=mysql_fetch_array($rs); To get a random record from earlier versions of MySQL you could do something like this. mt_srand((double)microtime()*100); $sql = select... ..where limit 0,50 $rs = mysql_query($sql); $num=mysql_num_rows($rs); if($num1) mysql_data_seek($rs,mt_rand(0,$num-1)); $row=mysql_fetch_array($rs); if you have a lot of records you can always put random numbers in the limit clause, (select ... where ...limit random_number, random_number + 50) this will return a random block of records, then use the seek functions to randomly pick a record from the record block, just make sure random_number isn't greater than the total records in your recordset. Pete Kelly - Original Message - From: Gurhan Ozen [EMAIL PROTECTED] To: Cummings, Shawn (GNAPs) [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 5:11 PM Subject: RE: Using RAND() You have to have MySQL 3.23 or greater.. http://www.mysql.com/doc/M/a/Mathematical_functions.html Gurhan -Original Message- From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 11:33 AM To: [EMAIL PROTECTED]; Ulf Harnhammar Cc: [EMAIL PROTECTED] Subject: Using RAND() The RAND() syntax does not appear to be working for me. What version mySQL is required? At 04:43 PM 1/29/2002 +0200, Michael Widenius wrote: Hi! Ulf == Ulf Harnhammar [EMAIL PROTECTED] writes: Ulf On Sun, 27 Jan 2002, Michael Widenius wrote: One way to do this is to do as follows: SELECT something FROM sometable WHERE somevar=somevalue ORDER BY RAND() LIMIT 10 - 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 - 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
Re: Using RAND()
At 11:32 -0400 5/7/02, Cummings, Shawn (GNAPs) wrote: The RAND() syntax does not appear to be working for me. What version mySQL is required? 3.23.2. Prior to that, use SELECT something, any_col*0+RAND() AS r FROM sometable WHERE ... ORDER BY r LIMIT 10 At 04:43 PM 1/29/2002 +0200, Michael Widenius wrote: Hi! Ulf == Ulf Harnhammar [EMAIL PROTECTED] writes: Ulf On Sun, 27 Jan 2002, Michael Widenius wrote: One way to do this is to do as follows: SELECT something FROM sometable WHERE somevar=somevalue ORDER BY RAND() LIMIT 10 - 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
RE: Using RAND()
Quoted from the manual page I have given below is: RAND() is not meant to be a perfect random generator, but instead a fast way to generate ad hoc random numbers that will be portable between platforms for the same MySQL version. So it was intentionally built so... Gurhan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 12:55 PM To: [EMAIL PROTECTED] Subject: Re: Using RAND() Hi When I used the RAND() function (MySQL 3.23.45) I found the results were not very random, instead I use an extra bit of php code to make sure the results are more random. mt_srand((double)microtime()*100); $sql = select order by RAND(.mt_rand(0,32767).) limit 50 $rs = mysql_query($sql); $num=mysql_num_rows($rs); if($num1) mysql_data_seek($rs,mt_rand(0,$num-1)); $row=mysql_fetch_array($rs); To get a random record from earlier versions of MySQL you could do something like this. mt_srand((double)microtime()*100); $sql = select... ..where limit 0,50 $rs = mysql_query($sql); $num=mysql_num_rows($rs); if($num1) mysql_data_seek($rs,mt_rand(0,$num-1)); $row=mysql_fetch_array($rs); if you have a lot of records you can always put random numbers in the limit clause, (select ... where ...limit random_number, random_number + 50) this will return a random block of records, then use the seek functions to randomly pick a record from the record block, just make sure random_number isn't greater than the total records in your recordset. Pete Kelly - Original Message - From: Gurhan Ozen [EMAIL PROTECTED] To: Cummings, Shawn (GNAPs) [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 5:11 PM Subject: RE: Using RAND() You have to have MySQL 3.23 or greater.. http://www.mysql.com/doc/M/a/Mathematical_functions.html Gurhan -Original Message- From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 11:33 AM To: [EMAIL PROTECTED]; Ulf Harnhammar Cc: [EMAIL PROTECTED] Subject: Using RAND() The RAND() syntax does not appear to be working for me. What version mySQL is required? At 04:43 PM 1/29/2002 +0200, Michael Widenius wrote: Hi! Ulf == Ulf Harnhammar [EMAIL PROTECTED] writes: Ulf On Sun, 27 Jan 2002, Michael Widenius wrote: One way to do this is to do as follows: SELECT something FROM sometable WHERE somevar=somevalue ORDER BY RAND() LIMIT 10 - 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 - 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
