RE: improving random record selection
>-Original Message- >From: Rob Wultsch [mailto:[EMAIL PROTECTED] >Sent: Monday, May 19, 2008 11:20 AM >To: Jerry Schwartz >Cc: Scott Haneda; mysql@lists.mysql.com >Subject: Re: improving random record selection > >On Mon, May 19, 2008 at 7:24 AM, Jerry Schwartz ><[EMAIL PROTECTED]> wrote: >> I might not understand what this is doing, but I think it will >preferentially sample the ids that are at the end of a gap. > >What don't you understand about the query or the way I described it? [JS] I was being cautious, I didn't have the wit or time to go over it in detail. > >> You say you want a "flat" distribution; by that I think you mean that >the probability of selecting any given record is the same. If you have >gaps in your data, I can't think of any way to do that other than be >assigning a unique and sequential ID to each record. If you ever delete >a record, you'd have to renumber the remaining ones. Then you'd pick off >a random value for this unique ID. > >There are alternatives. (generating a random number for each row for >example, take a look at the original conversation). Having to keep the >sequence holeless would be a pain in the back side, but could be done >with a trigger running something like I describe in the thread -> >http://lists.mysql.com/mysql/212838 . [JS] I think this would work: SET @rand_rec_num = (SELECT CAST(FLOOR(RAND() * COUNT(*) + 1) AS UNSIGNED) FROM bunya_map); PREPARE get_rand_rec FROM "SELECT * FROM bunya_map LIMIT ?, 1"; EXECUTE get_rand_rec USING @rand_rec_num; I suppose this could be put into a user function, if you only need a single value passed back. > >-- >Rob Wultsch >[EMAIL PROTECTED] >wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: improving random record selection
On Mon, May 19, 2008 at 7:24 AM, Jerry Schwartz <[EMAIL PROTECTED]> wrote: > I might not understand what this is doing, but I think it will preferentially > sample the ids that are at the end of a gap. What don't you understand about the query or the way I described it? > You say you want a "flat" distribution; by that I think you mean that the > probability of selecting any given record is the same. If you have gaps in > your data, I can't think of any way to do that other than be assigning a > unique and sequential ID to each record. If you ever delete a record, you'd > have to renumber the remaining ones. Then you'd pick off a random value for > this unique ID. There are alternatives. (generating a random number for each row for example, take a look at the original conversation). Having to keep the sequence holeless would be a pain in the back side, but could be done with a trigger running something like I describe in the thread -> http://lists.mysql.com/mysql/212838 . -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: improving random record selection
>-Original Message- >From: Rob Wultsch [mailto:[EMAIL PROTECTED] >Sent: Saturday, May 17, 2008 6:47 PM >To: Scott Haneda >Cc: mysql@lists.mysql.com >Subject: Re: improving random record selection > >On Sat, May 17, 2008 at 2:32 PM, Scott Haneda <[EMAIL PROTECTED]> >wrote: >>$sql = " >>SELECT storage_path, image_md5, t.id >>FROM images AS t >>JOIN >>(SELECT CEIL(MAX(id)*RAND()) AS id FROM images) >AS x >> ON (t.id >= x.id) >> AND (t.approved = 1) AND (t.ip_address != >> '$exclude_ip') LIMIT 1"; >I am going to reformat (whitespace only) your query a bit to start out >with: >SELECT storage_path, image_md5, t.id >FROM images AS t > JOIN ( > SELECT CEIL( > MAX(id)*RAND() > ) AS id > FROM images > ) AS x ON (t.id >= x.id) > AND (t.approved = 1) > AND (t.ip_address != '$exclude_ip') >LIMIT 1 > >I am going to break this up a bit: >SELECT storage_path, image_md5, t.id >FROM images AS t >JOIN >This should be mostly self explanatory. t.id specifies the table >because id is ambiguous (x.id is created later on). Please note that I >never use JOIN by itself. I would make this an INNER JOIN. > >SELECT CEIL( >MAX(id)*RAND() > ) AS id >FROM images AS x > >MAX(id) find the largest id that currently exists. This value is then >multiplied by whatever rand returns, which would be a between 0 and 1. >The result of the multiplication is then rounded up, and aliased as >id. The the table (of one row) is then aliased as x. So you now have >x.id which is a random number between 0 and the largest id value that >currently exists. > >ON (t.id >= x.id) > AND (t.approved = 1) > AND (t.ip_address != '$exclude_ip') >Finally we have your JOIN condition. It says, for the table aliased as >t, the id must be great than or equal to x.id (which was explained >above). This will eliminate some portion of the images table from the >possibility of being selected. Next all rows in the same table where >approved is not equal to 1 should be removed. Finally all rows that >fail t.ip_address != '$exclude_ip' get excluded. > >LIMIT 1 >Only return one row. > >Problems: >1. You should be using: > AND (t.approved = 1) > AND (t.ip_address != '$exclude_ip') >in the subquery. If x.id is larger than the largest row that fits >those conditions you will get no results. >2. There is no ORDER BY clause. There is nothing telling MySQL use the >t.id which is next largest value above x.id. MySQL will probably pick >out the right row, because they are probably stored in order. You >probably can get away with not having the ORDER BY clause, and it will >cost you extra cycles. How many extra cycles depends on how out of >order the table is. You can reorder the row by id using: >ALTER TABLE images ORDER BY id; >3. If the holes in your data are not distributed equally... > >Suggested new query: >SELECT storage_path, image_md5, t.id >FROM images AS t > INNER JOIN ( >SELECT CEIL( > MAX(id)*RAND() > ) AS id >FROM images >WHERE x.approved = 1 > AND x.ip_address != '$exclude_ip' > ) AS x ON (t.id >= x.id) >ORDER BY t.id ASC >LIMIT 1 > [JS] I might not understand what this is doing, but I think it will preferentially sample the ids that are at the end of a gap. >-- >Rob Wultsch >[EMAIL PROTECTED] >wultsch (aim) > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: improving random record selection
>From: Scott Haneda [mailto:[EMAIL PROTECTED] >Sent: Saturday, May 17, 2008 5:32 PM >To: mysql@lists.mysql.com >Subject: improving random record selection > >I posted this a month or so ago, and was helped a little, but I am now >back. > >Currently I use select x, y, z from images where (condition) order by >rand() limit 1; > >As most know, it is slow, depending on the record set, and what I >compare it to, it can be from one order of magnitude slower, to several. > >I have cobbled together a solution, but it on occasion, returns an >empty result set, which is causing me grief. I should mention, there >are holes in my id column, and I am needing to select a set based on a >condition. > >The below sql I do not fully understand either, if someone could step >me through it, I would certainly appreciate it, though my main goal is >to figure out why I get an empty set on occasion. > > $sql = " > SELECT storage_path, image_md5, t.id > FROM images AS t > JOIN > (SELECT CEIL(MAX(id)*RAND()) AS id FROM images) AS x >ON (t.id >= >x.id) >AND (t.approved = 1) AND (t.ip_address != >'$exclude_ip') LIMIT 1"; > >* I could almost live with the slow speed of an order by rand() but I >find it has a less than even distribution. While it indeed may be >very random, I am looking for a pretty flat response in distribution >of returned records over time. [JS] You say you want a "flat" distribution; by that I think you mean that the probability of selecting any given record is the same. If you have gaps in your data, I can't think of any way to do that other than be assigning a unique and sequential ID to each record. If you ever delete a record, you'd have to renumber the remaining ones. Then you'd pick off a random value for this unique ID. At first glance, this seems to be the only way to avoid sampling errors. If there were some way of setting a cursor to an arbitrary record, that would work very well; but you don't want to be stepping sequentially through (on average) half or your records. >-- >Scott >[EMAIL PROTECTED] > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: improving random record selection
On Sat, May 17, 2008 at 2:32 PM, Scott Haneda <[EMAIL PROTECTED]> wrote: >$sql = " >SELECT storage_path, image_md5, t.id >FROM images AS t >JOIN >(SELECT CEIL(MAX(id)*RAND()) AS id FROM images) AS x > ON (t.id >= x.id) > AND (t.approved = 1) AND (t.ip_address != > '$exclude_ip') LIMIT 1"; I am going to reformat (whitespace only) your query a bit to start out with: SELECT storage_path, image_md5, t.id FROM images AS t JOIN ( SELECT CEIL( MAX(id)*RAND() ) AS id FROM images ) AS x ON (t.id >= x.id) AND (t.approved = 1) AND (t.ip_address != '$exclude_ip') LIMIT 1 I am going to break this up a bit: SELECT storage_path, image_md5, t.id FROM images AS t JOIN This should be mostly self explanatory. t.id specifies the table because id is ambiguous (x.id is created later on). Please note that I never use JOIN by itself. I would make this an INNER JOIN. SELECT CEIL( MAX(id)*RAND() ) AS id FROM images AS x MAX(id) find the largest id that currently exists. This value is then multiplied by whatever rand returns, which would be a between 0 and 1. The result of the multiplication is then rounded up, and aliased as id. The the table (of one row) is then aliased as x. So you now have x.id which is a random number between 0 and the largest id value that currently exists. ON (t.id >= x.id) AND (t.approved = 1) AND (t.ip_address != '$exclude_ip') Finally we have your JOIN condition. It says, for the table aliased as t, the id must be great than or equal to x.id (which was explained above). This will eliminate some portion of the images table from the possibility of being selected. Next all rows in the same table where approved is not equal to 1 should be removed. Finally all rows that fail t.ip_address != '$exclude_ip' get excluded. LIMIT 1 Only return one row. Problems: 1. You should be using: AND (t.approved = 1) AND (t.ip_address != '$exclude_ip') in the subquery. If x.id is larger than the largest row that fits those conditions you will get no results. 2. There is no ORDER BY clause. There is nothing telling MySQL use the t.id which is next largest value above x.id. MySQL will probably pick out the right row, because they are probably stored in order. You probably can get away with not having the ORDER BY clause, and it will cost you extra cycles. How many extra cycles depends on how out of order the table is. You can reorder the row by id using: ALTER TABLE images ORDER BY id; 3. If the holes in your data are not distributed equally... Suggested new query: SELECT storage_path, image_md5, t.id FROM images AS t INNER JOIN ( SELECT CEIL( MAX(id)*RAND() ) AS id FROM images WHERE x.approved = 1 AND x.ip_address != '$exclude_ip' ) AS x ON (t.id >= x.id) ORDER BY t.id ASC LIMIT 1 -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
improving random record selection
I posted this a month or so ago, and was helped a little, but I am now back. Currently I use select x, y, z from images where (condition) order by rand() limit 1; As most know, it is slow, depending on the record set, and what I compare it to, it can be from one order of magnitude slower, to several. I have cobbled together a solution, but it on occasion, returns an empty result set, which is causing me grief. I should mention, there are holes in my id column, and I am needing to select a set based on a condition. The below sql I do not fully understand either, if someone could step me through it, I would certainly appreciate it, though my main goal is to figure out why I get an empty set on occasion. $sql = " SELECT storage_path, image_md5, t.id FROM images AS t JOIN (SELECT CEIL(MAX(id)*RAND()) AS id FROM images) AS x ON (t.id >= x.id) AND (t.approved = 1) AND (t.ip_address != '$exclude_ip') LIMIT 1"; * I could almost live with the slow speed of an order by rand() but I find it has a less than even distribution. While it indeed may be very random, I am looking for a pretty flat response in distribution of returned records over time. -- Scott [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Choose a random record from a list of duplicates
> zv Green wrote: >> Hello all, >> >> What I want to do is select all the records from the table but where >> there >> are duplicate entries (based on say, the surname and postcode fields) >> pick a random record and then ignore the rest. > > If you want to pick a random record, you can do ORDER BY RANDOM LIMIT 1 > or so. (warning, untested) And I think you will want to group by surname and postcode in order to filter out the duplicates. -- - Scott HanedaTel: 415.898.2602 <http://www.newgeo.com> Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Choose a random record from a list of duplicates
zv Green wrote: Hello all, What I want to do is select all the records from the table but where there are duplicate entries (based on say, the surname and postcode fields) pick a random record and then ignore the rest. If you want to pick a random record, you can do ORDER BY RANDOM LIMIT 1 or so. (warning, untested) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Choose a random record from a list of duplicates
Hello all, I'm having real problems trying to work this one out. Basically I have a big table full of names, addresses and other such information. What I want to do is select all the records from the table but where there are duplicate entries (based on say, the surname and postcode fields) pick a random record and then ignore the rest. Lets say i have 2000 records in the table but 5 of them are dupes based on the surname and postcode, how can I pick one of those 5 at random and return it with the rest of the table. Any help would be greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficiently finding a random record
Michael Stassen wrote: For example, if the selected random id is missing, we take the next id we find, like this: SELECT @rand_id:= CAST( 1 + MAX(id)*RAND() AS UNSIGNED) FROM history; SELECT * FROM history WHERE id >= @rand_id LIMIT 1; That will have a possibly undesired effect. Records that have gaps in the IDs before them will be twice, three times, etc. (depending on the size of the gap), as likely to be selected as records with no preceding gaps. Replace MAX with COUNT and the WHERE clause with an OFFSET and the gap problem should go away... -philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficiently finding a random record
Michael Stassen wrote: For example, if the selected random id is missing, we take the next id we find, like this: SELECT @rand_id:= CAST( 1 + MAX(id)*RAND() AS UNSIGNED) FROM history; SELECT * FROM history WHERE id >= @rand_id LIMIT 1; That will have a possibly undesired effect. Records that have gaps in the IDs before them will be twice, three times, etc. (depending on the size of the gap), as likely to be selected as records with no preceding gaps. -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficiently finding a random record
Eric Bergen wrote: in() can take millions of arguments. Up to max packet size. Try it :) True, but several past threads have suggested that performance drops dramatically when the size of the IN list gets too large. As IN is equivalent to an equality check for each value in the list, separated by ORs, this is not surprising. Dan Bolser wrote: On Fri, 13 May 2005, Eric Bergen wrote: Even better is if you have an integer primary key (think auto_increment) and use in() So if you want 10 random rows in your app generate 20 or so random numbers and do something like select col1, from t where x in (1, 5, 3, 2...) limit 10 check num rows and if you don't get enough generate more random numbers and try again. in() is blazing fast even with thousands of numbers so don't be afraid to kick a few extra in. I heard about a 255 'in' limit. When you say 'thousands of numbers' do you mean in the IN or in the column? -Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficiently finding a random record
You are both missing something fundamental: functions are evaluated for each and every row. Your query, SELECT * FROM history WHERE id = CAST(RAND() * 2355008 AS UNSIGNED); gets a random number for *each row*, and returns the row if that row's random number is the same as its id. You are rolling the dice on each row. Because the odds of a match are 1/2355008 and you are rolling the dice 2355008 times, the *expected* number of matches is 1. That is, *on average*, this will return one row, but the outcome on any single running of this query will vary. (This also requires a full table scan, making it slow.) You want to choose 1 random number, then choose the row with that id. Assuming there are no gaps (not a good assumption in usual practice), this can be accomplished with a user variable and two statements: SET @rand_id = CAST(1 + 2355008*RAND() AS UNSIGNED); SELECT * FROM history WHERE id = @rand_id; Because ensuring that there are no gaps in your ids usually requires monkeying with the ids in ways that are considered bad practice, a query that doesn't make that assumption would be better. One solution would be to modify the query to allow several rows to match and then take the first one (using LIMIT). For example, if the selected random id is missing, we take the next id we find, like this: SELECT @rand_id:= CAST( 1 + MAX(id)*RAND() AS UNSIGNED) FROM history; SELECT * FROM history WHERE id >= @rand_id LIMIT 1; Both queries should use the index on id and return quickly. Michael Gary Huntress wrote: There's definitely more going on here that I don't understand. Consider this simple query and result: mysql> select * from history where id = CAST( rand() * 2355008 as unsigned); +-+++---++ | id | symbol | thedate| close | volume | +-+++---++ | 1187308 | MAN| 2003-05-23 | 33.13 | 444800 | | 1398426 | NHP| 2003-08-26 | 14.69 | 215200 | | 2176684 | UMPQ | 2004-01-27 | 20.74 | 43500 | +-+++---++ 3 rows in set (2.67 sec) ID is an autoincrement field, and there are 2355008 total unique records, yet even though the where clause is a test for equivalence I get three result records. I think I reasonably expected to get 1 and only 1 record. Puzzled, Gary - Original Message - From: <[EMAIL PROTECTED]> To: "Gary Huntress" <[EMAIL PROTECTED]> Cc: Sent: Sunday, May 15, 2005 6:42 AM Subject: Re: Efficiently finding a random record Hi, i did the test and agree with you. even with Select ID from history where id = 1+CAST( rand() * 19 as UNSIGNED) limit 1; when testing : select CAST( rand() * 19 as UNSIGNED) this always returns a unique value. So you don't need limit 1. The workaround i found is : Select ID from history where id >= 1+CAST( rand() * 19 as UNSIGNED) limit 1; here limit 1 is usefull, and there no empty set. Mathias Selon Gary Huntress <[EMAIL PROTECTED]>: As it happens, I found this post useful tonight for an almost identical situation. I have a table with exactly 200,000 rows, the first column is an autoincrement ID field. I am confident that all IDs are consecutive and there are no holes. When I do: Select ID from history where id = CAST( rand() * 19 + 1 as UNSIGNED) limit 1; shouldn't it always return a single record? The CAST id should always be in the range from 1 to 20. About 50% of the time I get an empty recordset and I'm puzzled. (could be lack of sleep though) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficiently finding a random record
There's definitely more going on here that I don't understand. Consider this simple query and result: mysql> select * from history where id = CAST( rand() * 2355008 as unsigned); +-+++---++ | id | symbol | thedate| close | volume | +-+++---++ | 1187308 | MAN| 2003-05-23 | 33.13 | 444800 | | 1398426 | NHP| 2003-08-26 | 14.69 | 215200 | | 2176684 | UMPQ | 2004-01-27 | 20.74 | 43500 | +-+++---++ 3 rows in set (2.67 sec) ID is an autoincrement field, and there are 2355008 total unique records, yet even though the where clause is a test for equivalence I get three result records. I think I reasonably expected to get 1 and only 1 record. Puzzled, Gary - Original Message - From: <[EMAIL PROTECTED]> To: "Gary Huntress" <[EMAIL PROTECTED]> Cc: Sent: Sunday, May 15, 2005 6:42 AM Subject: Re: Efficiently finding a random record Hi, i did the test and agree with you. even with Select ID from history where id = 1+CAST( rand() * 19 as UNSIGNED) limit 1; when testing : select CAST( rand() * 19 as UNSIGNED) this always returns a unique value. So you don't need limit 1. The workaround i found is : Select ID from history where id >= 1+CAST( rand() * 19 as UNSIGNED) limit 1; here limit 1 is usefull, and there no empty set. Mathias Selon Gary Huntress <[EMAIL PROTECTED]>: As it happens, I found this post useful tonight for an almost identical situation. I have a table with exactly 200,000 rows, the first column is an autoincrement ID field. I am confident that all IDs are consecutive and there are no holes. When I do: Select ID from history where id = CAST( rand() * 19 + 1 as UNSIGNED) limit 1; shouldn't it always return a single record? The CAST id should always be in the range from 1 to 20. About 50% of the time I get an empty recordset and I'm puzzled. (could be lack of sleep though) Gary - Original Message - From: "Eric Bergen" <[EMAIL PROTECTED]> To: "Dan Bolser" <[EMAIL PROTECTED]> Cc: "Philip Hallstrom" <[EMAIL PROTECTED]>; "Brian Dunning" <[EMAIL PROTECTED]>; Sent: Friday, May 13, 2005 7:48 PM Subject: Re: Efficiently finding a random record > in() can take millions of arguments. Up to max packet size. Try it :) > > Dan Bolser wrote: > >>On Fri, 13 May 2005, Eric Bergen wrote: >> >> >>>Even better is if you have an integer primary key (think >>>auto_increment) >>>and use in() >>> >>>So if you want 10 random rows in your app generate 20 or so random >>>numbers and do something like >>>select col1, from t where x in (1, 5, 3, 2...) limit 10 >>> >>>check num rows and if you don't get enough generate more random >>>numbers >>>and try again. >>> >>>in() is blazing fast even with thousands of numbers so don't be afraid >>>to >>>kick a few extra in. >>> >> >>I heard about a 255 'in' limit. When you say 'thousands of numbers' do >>you >>mean in the IN or in the column? >> >> >> >>>-Eric >>> >>> >>>Philip Hallstrom wrote: >>> >>> >>>>>I have a db of about 300,000 records and when I try to find one >>>>>random >>>>>record like this: >>>>> >>>>>select * from table order by rand() limit 1; >>>>> >>>>>it can take several minutes. My Sherlock Holmes instincts tell me >>>>>that >>>>>what I'm doing is somehow inefficient. What is the primary culprit >>>>>here? >>>>> >>>>The culprit is that mysql has to assign a random value (via rand()) >>>>to >>>>each of the 300,000 rows, then order all 300,000 rows by that random >>>>value and return the first one. >>>> >>>>So even though you're just retrieving one value, you're processing >>>>300,000 rows. >>>> >>>>You'd be better off doing something like this in your application.. >>>> >>>>row_count = get row count; >>>>random_row = get random number from 0 to row_count - 1; >>>>result = db query LIMIT 1 OFFSET random_row >>>> >>>>or something like that... >>>> >>>>-philip >>>> >>>> >>> >>> >> >> >> > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficiently finding a random record
Hi, i did the test and agree with you. even with Select ID from history where id = 1+CAST( rand() * 19 as UNSIGNED) limit 1; when testing : select CAST( rand() * 19 as UNSIGNED) this always returns a unique value. So you don't need limit 1. The workaround i found is : Select ID from history where id >= 1+CAST( rand() * 19 as UNSIGNED) limit 1; here limit 1 is usefull, and there no empty set. Mathias Selon Gary Huntress <[EMAIL PROTECTED]>: > As it happens, I found this post useful tonight for an almost identical > situation. > > I have a table with exactly 200,000 rows, the first column is an > autoincrement ID field. I am confident that all IDs are consecutive and > there are no holes. > > When I do: > > Select ID from history where id = CAST( rand() * 19 + 1 as UNSIGNED) > limit 1; > > shouldn't it always return a single record? The CAST id should always be in > the range from 1 to 20. > > About 50% of the time I get an empty recordset and I'm puzzled. (could be > lack of sleep though) > > > Gary > - Original Message - > From: "Eric Bergen" <[EMAIL PROTECTED]> > To: "Dan Bolser" <[EMAIL PROTECTED]> > Cc: "Philip Hallstrom" <[EMAIL PROTECTED]>; "Brian Dunning" > <[EMAIL PROTECTED]>; > Sent: Friday, May 13, 2005 7:48 PM > Subject: Re: Efficiently finding a random record > > > > in() can take millions of arguments. Up to max packet size. Try it :) > > > > Dan Bolser wrote: > > > >>On Fri, 13 May 2005, Eric Bergen wrote: > >> > >> > >>>Even better is if you have an integer primary key (think auto_increment) > >>>and use in() > >>> > >>>So if you want 10 random rows in your app generate 20 or so random > >>>numbers and do something like > >>>select col1, from t where x in (1, 5, 3, 2...) limit 10 > >>> > >>>check num rows and if you don't get enough generate more random numbers > >>>and try again. > >>> > >>>in() is blazing fast even with thousands of numbers so don't be afraid to > >>>kick a few extra in. > >>> > >> > >>I heard about a 255 'in' limit. When you say 'thousands of numbers' do you > >>mean in the IN or in the column? > >> > >> > >> > >>>-Eric > >>> > >>> > >>>Philip Hallstrom wrote: > >>> > >>> > >>>>>I have a db of about 300,000 records and when I try to find one random > >>>>>record like this: > >>>>> > >>>>>select * from table order by rand() limit 1; > >>>>> > >>>>>it can take several minutes. My Sherlock Holmes instincts tell me that > >>>>>what I'm doing is somehow inefficient. What is the primary culprit > >>>>>here? > >>>>> > >>>>The culprit is that mysql has to assign a random value (via rand()) to > >>>>each of the 300,000 rows, then order all 300,000 rows by that random > >>>>value and return the first one. > >>>> > >>>>So even though you're just retrieving one value, you're processing > >>>>300,000 rows. > >>>> > >>>>You'd be better off doing something like this in your application.. > >>>> > >>>>row_count = get row count; > >>>>random_row = get random number from 0 to row_count - 1; > >>>>result = db query LIMIT 1 OFFSET random_row > >>>> > >>>>or something like that... > >>>> > >>>>-philip > >>>> > >>>> > >>> > >>> > >> > >> > >> > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficiently finding a random record
As it happens, I found this post useful tonight for an almost identical situation. I have a table with exactly 200,000 rows, the first column is an autoincrement ID field. I am confident that all IDs are consecutive and there are no holes. When I do: Select ID from history where id = CAST( rand() * 19 + 1 as UNSIGNED) limit 1; shouldn't it always return a single record? The CAST id should always be in the range from 1 to 20. About 50% of the time I get an empty recordset and I'm puzzled. (could be lack of sleep though) Gary - Original Message - From: "Eric Bergen" <[EMAIL PROTECTED]> To: "Dan Bolser" <[EMAIL PROTECTED]> Cc: "Philip Hallstrom" <[EMAIL PROTECTED]>; "Brian Dunning" <[EMAIL PROTECTED]>; Sent: Friday, May 13, 2005 7:48 PM Subject: Re: Efficiently finding a random record in() can take millions of arguments. Up to max packet size. Try it :) Dan Bolser wrote: On Fri, 13 May 2005, Eric Bergen wrote: Even better is if you have an integer primary key (think auto_increment) and use in() So if you want 10 random rows in your app generate 20 or so random numbers and do something like select col1, from t where x in (1, 5, 3, 2...) limit 10 check num rows and if you don't get enough generate more random numbers and try again. in() is blazing fast even with thousands of numbers so don't be afraid to kick a few extra in. I heard about a 255 'in' limit. When you say 'thousands of numbers' do you mean in the IN or in the column? -Eric Philip Hallstrom wrote: I have a db of about 300,000 records and when I try to find one random record like this: select * from table order by rand() limit 1; it can take several minutes. My Sherlock Holmes instincts tell me that what I'm doing is somehow inefficient. What is the primary culprit here? The culprit is that mysql has to assign a random value (via rand()) to each of the 300,000 rows, then order all 300,000 rows by that random value and return the first one. So even though you're just retrieving one value, you're processing 300,000 rows. You'd be better off doing something like this in your application.. row_count = get row count; random_row = get random number from 0 to row_count - 1; result = db query LIMIT 1 OFFSET random_row or something like that... -philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficiently finding a random record
in() can take millions of arguments. Up to max packet size. Try it :) Dan Bolser wrote: On Fri, 13 May 2005, Eric Bergen wrote: Even better is if you have an integer primary key (think auto_increment) and use in() So if you want 10 random rows in your app generate 20 or so random numbers and do something like select col1, from t where x in (1, 5, 3, 2...) limit 10 check num rows and if you don't get enough generate more random numbers and try again. in() is blazing fast even with thousands of numbers so don't be afraid to kick a few extra in. I heard about a 255 'in' limit. When you say 'thousands of numbers' do you mean in the IN or in the column? -Eric Philip Hallstrom wrote: I have a db of about 300,000 records and when I try to find one random record like this: select * from table order by rand() limit 1; it can take several minutes. My Sherlock Holmes instincts tell me that what I'm doing is somehow inefficient. What is the primary culprit here? The culprit is that mysql has to assign a random value (via rand()) to each of the 300,000 rows, then order all 300,000 rows by that random value and return the first one. So even though you're just retrieving one value, you're processing 300,000 rows. You'd be better off doing something like this in your application.. row_count = get row count; random_row = get random number from 0 to row_count - 1; result = db query LIMIT 1 OFFSET random_row or something like that... -philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficiently finding a random record
On Fri, 13 May 2005, Eric Bergen wrote: >Even better is if you have an integer primary key (think auto_increment) >and use in() > >So if you want 10 random rows in your app generate 20 or so random >numbers and do something like >select col1, from t where x in (1, 5, 3, 2...) limit 10 > >check num rows and if you don't get enough generate more random numbers >and try again. > >in() is blazing fast even with thousands of numbers so don't be afraid >to kick a few extra in. I heard about a 255 'in' limit. When you say 'thousands of numbers' do you mean in the IN or in the column? > >-Eric > > >Philip Hallstrom wrote: > >>> I have a db of about 300,000 records and when I try to find one >>> random record like this: >>> >>> select * from table order by rand() limit 1; >>> >>> it can take several minutes. My Sherlock Holmes instincts tell me >>> that what I'm doing is somehow inefficient. What is the primary >>> culprit here? >> >> >> The culprit is that mysql has to assign a random value (via rand()) to >> each of the 300,000 rows, then order all 300,000 rows by that random >> value and return the first one. >> >> So even though you're just retrieving one value, you're processing >> 300,000 rows. >> >> You'd be better off doing something like this in your application.. >> >> row_count = get row count; >> random_row = get random number from 0 to row_count - 1; >> result = db query LIMIT 1 OFFSET random_row >> >> or something like that... >> >> -philip >> > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficiently finding a random record
Even better is if you have an integer primary key (think auto_increment) and use in() So if you want 10 random rows in your app generate 20 or so random numbers and do something like select col1, from t where x in (1, 5, 3, 2...) limit 10 check num rows and if you don't get enough generate more random numbers and try again. in() is blazing fast even with thousands of numbers so don't be afraid to kick a few extra in. -Eric Philip Hallstrom wrote: I have a db of about 300,000 records and when I try to find one random record like this: select * from table order by rand() limit 1; it can take several minutes. My Sherlock Holmes instincts tell me that what I'm doing is somehow inefficient. What is the primary culprit here? The culprit is that mysql has to assign a random value (via rand()) to each of the 300,000 rows, then order all 300,000 rows by that random value and return the first one. So even though you're just retrieving one value, you're processing 300,000 rows. You'd be better off doing something like this in your application.. row_count = get row count; random_row = get random number from 0 to row_count - 1; result = db query LIMIT 1 OFFSET random_row or something like that... -philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficiently finding a random record
I have a db of about 300,000 records and when I try to find one random record like this: select * from table order by rand() limit 1; it can take several minutes. My Sherlock Holmes instincts tell me that what I'm doing is somehow inefficient. What is the primary culprit here? The culprit is that mysql has to assign a random value (via rand()) to each of the 300,000 rows, then order all 300,000 rows by that random value and return the first one. So even though you're just retrieving one value, you're processing 300,000 rows. You'd be better off doing something like this in your application.. row_count = get row count; random_row = get random number from 0 to row_count - 1; result = db query LIMIT 1 OFFSET random_row or something like that... -philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficiently finding a random record
At 12:54 PM 5/13/05, Brian Dunning wrote: I have a db of about 300,000 records and when I try to find one random record like this: select * from table order by rand() limit 1; it can take several minutes. My Sherlock Holmes instincts tell me that what I'm doing is somehow inefficient. What is the primary culprit here? Sherlock Holmes would have checked the archives first! There was a discussion about this a couple of weeks ago - Apr.26 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Efficiently finding a random record
I have a db of about 300,000 records and when I try to find one random record like this: select * from table order by rand() limit 1; it can take several minutes. My Sherlock Holmes instincts tell me that what I'm doing is somehow inefficient. What is the primary culprit here? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting a random record from more than 1 table
I'm putting this discussion back on the list so that others can learn from it and/or contribute to it. So, have I got this right? You have two completely independent and unrelated tables, one containing Phones and one containing Other products and you want to choose a single one at random from the combination of both tables. Your remark about "sql-version up to date" isn't very helpful. Does that mean it is the latest version in the 3.x stream? The 4.0.x stream? The 4.1.x stream? Or the 5.x stream? The version number always appears when you start MySQL, e.g. mysql -u myuserid -p mypswd. Assuming you are using MySQL Version 4.0.x, the obvious solution to your problem is to combine both tables together into a single table via UNION, then choose 1 row from the combined table at random. (There may be better solutions - like choosing a row at random from Phones, choosing a row at random from Others, and then combinining those two rows via UNION and choosing one of them at random - but I'll let others suggest those solutions. And I'll leave it to you to choose the one you like best, based either on simplicity of the solution or performance.) To choose one row at random from a single table, you do this: select * from Phones order by rand() limit 1; If you repeat this query against your table several times, you should find that you usually get a different row each time, although you can choose the same row at random two or three times in a row too. To combine two tables that are not related, as appears to be the case in your situation, you use UNION, not join. The basic look of UNION is this: select * from Table1 UNION select * from Table2 optionally followed by an ORDER BY. The ORDER BY applies to the entire combined result of the UNION, NOT just to the last query. However, UNION has some special rules that have to be observed to use it successfully: the only way the example would work is if Table1 and Table2 had the same number of columns and the columns lined up the same way. For example, if Table1 contained an int, a CHAR, and another int in that order, Table2 would also need to contain a numeric value, a character type column, and another numeric value in that order for the UNION to work. As a result, you don't often use "Select *" in UNIONs because the two tables rarely line up precisely the same way. More often, you will see only specific columns in each select, specifically chosen to line up as I described. Therefore, if Phones contained an integer Product_ID column and Others contained a smallint Part_Number, you'd do this: select Product_ID from Phones UNION select Part_Number from Others If you imitate that reasoning with your UNION, you should get a viable result. Then, add the 'ORDER BY rand() limit 1' to the end of the query and you should be away to the races. For example, I have two "play" tables, one called Employee and the other called emp. To choose one row at random from the UNIONed result, I did this: select empno, lastname from Employee UNION select empno, lastname from emp order by rand() limit 1; It worked perfectly. I don't know when the rand() function and the 'limit' option were added to MySQL so if you are running an oldish version of MySQL, these techniques may not work for you. (I am running MySQL 4.0.15). In that case, please post your version and maybe someone else can suggest something that would work for you. Rhino - Original Message - From: "Christian Biggins" <[EMAIL PROTECTED]> To: "'Rhino'" <[EMAIL PROTECTED]> Sent: Friday, January 14, 2005 11:43 AM Subject: RE: Selecting a random record from more than 1 table > Hi Rhino, > > Basically, that's exactly what I am trying to do. 2 tables with different > designs and different records and I want to select 1 random record from > either of them. So, I have a phones and a Products table and on the main > page of a site I want a 'random product' box... See what I mean? > > Um, sql version is up-to-date, whichever the latest is. > > Thanks. > > Christian > > -Original Message- > From: Rhino [mailto:[EMAIL PROTECTED] > Sent: Saturday, 15 January 2005 12:56 AM > To: Christian Biggins; mysql@lists.mysql.com > Subject: Re: Selecting a random record from more than 1 table > > > - Original Message - > From: "Christian Biggins" <[EMAIL PROTECTED]> > To: > Sent: Friday, January 14, 2005 12:07 AM > Subject: Selecting a random record from more than 1 table > > > > Hi Guys > > > > I am trying to display 1 random record taken from 2 tables. > > > > I have tried the following; > > > > SELECT table1.record1, table1.record2, table2.record1 FROM table1, table2 > > ORDER BY RAND() Limit 1 > > > > With n
Re: Selecting a random record from more than 1 table
- Original Message - From: "Christian Biggins" <[EMAIL PROTECTED]> To: Sent: Friday, January 14, 2005 12:07 AM Subject: Selecting a random record from more than 1 table > Hi Guys > > I am trying to display 1 random record taken from 2 tables. > > I have tried the following; > > SELECT table1.record1, table1.record2, table2.record1 FROM table1, table2 > ORDER BY RAND() Limit 1 > > With no luck... So now I am trying to use CREATE VIEW but also with no > luck... Can anybody help out? > Your question isn't very clear; I can't make out what you are trying to do. Can you explain in a bit more detail? It sounds like you are trying to do a join of two tables which selects all records from both which match and then choose one of the resulting records at random. But, if that is true, your query contains a bad join because it is actually going to join every row of Table1 with every row of Table2 *even if they haven nothing in common*. I'm not sure why you want to create a view either. Also, what version of MySQL are you using? Some things can't be done at all in some versions of MySQL Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting a random record from more than 1 table
Hi Guys I am trying to display 1 random record taken from 2 tables. I have tried the following; SELECT table1.record1, table1.record2, table2.record1 FROM table1, table2 ORDER BY RAND() Limit 1 With no luck... So now I am trying to use CREATE VIEW but also with no luck... Can anybody help out? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random Record Retrieval
mos wrote: At 05:08 PM 5/22/2004, you wrote: Robb Kerr wrote: Got a simple table that contains records which are made up of only three fields - ID, quote and author. These are "inspirational" quotes that I want to appear at the bottom of the pages of my website. I want them to come up randomly with every page load. How do I randomly access records from a table? SELECT ID,quote,author from quotes ORDER BY RAND() LIMIT 1 HTH! -- Unfortunately that will sort the entire table even though it returns only 1 row so it may be too slow for a website if there are a lot of rows in the table. You will need to create an indexed autoinc field field (if you don't already have one) and use PHP's Math.Random function to pick a single row from the table. The table's autoinc sequence should not have any holes in it. See http://www.phpfreaks.com/quickcode/Random_numbers/87.php for a PHP example on how to use Random. Mike You say, "The table's autoinc sequence should not have any holes in it." I fear it isn't clear, however, that that's a requirement to make the code simple, rather than a statement of fact. Unless you never delete a row, or go to great lengths to always reuse deleted IDs, there will be holes in the autoinc sequence, so you will need to code the script to handle the case that the randomly chosen ID doesn't exist. At a minimum, then, your code will do 2 SELECTs to get the quote, one to get the MAX ID, then a loop to get a random ID from 1 to MAX until you get a hit (usually, but not always, on the first try, depending on how many IDs are missing). If you can live with the quote changing frequently, rather than with every page load, there is an alternative. You could add a column to keep a random order and update it periodically. Something like: ALTER TABLE quotes ADD rand_order FLOAT; UPDATE quotes SET rand_order=RAND(); Then you can get always get a quote with one SELECT: SELECT ID,quote,author FROM quotes ORDER BY rand_order LIMIT 1; Of course, that will keep returning the same quote, so you set up a cron job to periodically (every 5 minutes, for example) run an UPDATE quotes SET rand_order=RAND(); to change the ordering, effectively choosing a new quote at random. I suppose if you really wanted a random quote with every page, you could have each page run the UPDATE after running the SELECT, though I suspect that would be no more efficient than the original solution. Michael P.S. As a mathematician and programmer, I find the phpfreaks description of random numbers annoyingly imprecise. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random Record Retrieval
At 05:08 PM 5/22/2004, you wrote: Robb Kerr wrote: Got a simple table that contains records which are made up of only three fields - ID, quote and author. These are "inspirational" quotes that I want to appear at the bottom of the pages of my website. I want them to come up randomly with every page load. How do I randomly access records from a table? SELECT ID,quote,author from quotes ORDER BY RAND() LIMIT 1 HTH! -- Unfortunately that will sort the entire table even though it returns only 1 row so it may be too slow for a website if there are a lot of rows in the table. You will need to create an indexed autoinc field field (if you don't already have one) and use PHP's Math.Random function to pick a single row from the table. The table's autoinc sequence should not have any holes in it. See http://www.phpfreaks.com/quickcode/Random_numbers/87.php for a PHP example on how to use Random. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random Record Retrieval
Robb Kerr wrote: Got a simple table that contains records which are made up of only three fields - ID, quote and author. These are "inspirational" quotes that I want to appear at the bottom of the pages of my website. I want them to come up randomly with every page load. How do I randomly access records from a table? SELECT ID,quote,author from quotes ORDER BY RAND() LIMIT 1 HTH! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Random Record Retrieval
How's that for alliteration in a subject line? Got a simple table that contains records which are made up of only three fields - ID, quote and author. These are "inspirational" quotes that I want to appear at the bottom of the pages of my website. I want them to come up randomly with every page load. How do I randomly access records from a table? I thought about building a random number generator in PHP that selects a random number out of the total number of records in the table, saves it into a variable and then have MySQL retrieve that ID. Is this the way to go or is there something simpler. Unfortunately I don't know how to write a random number generator in PHP. Thanx in advance, Robb -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.cancerreallysucks.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: random record
I just checked... 83 On Mon, 15 Sep 2003 13:54:53 +0100, "Andy Eastham" <[EMAIL PROTECTED]> wrote: | 39? | | > -Original Message- | > From: tuncay bas [mailto:[EMAIL PROTECTED] | > Sent: 15 September 2003 13:32 | > To: mysql | > Subject: random record | > | > | > hi, | > | > why its mysql database over random record use? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: {Scanned} RE: random record
pls unsubscribe me from this maillist. thanks.
- Original Message -
From: "Andy Eastham" <[EMAIL PROTECTED]>
To: "Mysql List" <[EMAIL PROTECTED]>
Sent: Monday, September 15, 2003 8:54 PM
Subject: {Scanned} RE: random record
> 39?
>
> > -Original Message-
> > From: tuncay bas [mailto:[EMAIL PROTECTED]
> > Sent: 15 September 2003 13:32
> > To: mysql
> > Subject: random record
> >
> >
> > hi,
> >
> > why its mysql database over random record use?
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
RE: random record
39? > -Original Message- > From: tuncay bas [mailto:[EMAIL PROTECTED] > Sent: 15 September 2003 13:32 > To: mysql > Subject: random record > > > hi, > > why its mysql database over random record use? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
random record
hi, why its mysql database over random record use?
random record
hi, why its mysql database over random record use?
Re: weighted random record select?
If you have a field 'weight', you could SELECT *, RAND()*weight AS r FROM table_name ORDER BY r (Or you can complicate the expression more :-) laszlo Ed Lazor wrote: Ed Lazor wrote: > I found this: > SELECT * FROM table_name ORDER BY RAND() > > Is there a way to weight the random selection so that certain records are > more likely to come up? > > It's for a banner exchange program. The idea is to somehow give precedence > to help promote certain sites. > > Thanks! =) > > -Ed > > - > 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
weighted random record select?
I found this: SELECT * FROM table_name ORDER BY RAND() Is there a way to weight the random selection so that certain records are more likely to come up? It's for a banner exchange program. The idea is to somehow give precedence to help promote certain sites. Thanks! =) -Ed - 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: Random record
You have to have version 3.23 of mysql and then you can add ORDER BY RAND() to the end of your query. Alan -Original Message- From: joe [mailto:[EMAIL PROTECTED]] Sent: Sunday, February 25, 2018 4:40 PM To: MySQL Subject: Random record Does anyone have any good ideas on how to pull a random record? I've been using PHP's random function to create a random number to pull, but the problem is that if I delete a record, it could still try to pull it. Is there a better way? I'd really like to be able to just pull it in the SQL statement instead of pulling a bunch of records then sorting throught that. Thanks, JOE - 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
Select a Random record
Dear MySQL masters, Is there any way to display a random record from a table. If there are no direct SQL commands can you pls suggest a logic for doing the same. Details: There is a table of 200 records. The user must be able to select a record randomly. Once that record is displayed then that record will not be available for the next query. (this I am planning to do with a flag field and adding this in the query statement). SELECT * from $table WHERE flag != 'TRUE'; I am using PHP to retrieve the records. so the PHP function mysql_fetch_array($query); should retrieve a random record, not a record in any particular order (ORDER BY). If anybody has got any suggestions on this please send it to me. thanks Sheni R Meledath [EMAIL PROTECTED] - 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: Random record
Instead of pulling using equals, ie "select * from table where id = $php_random_variable" you can do this instead "select * from table where id <= $php_random_variable LIMIT 1"... As long is there is atleast one row in the table you will get back a result. Hope that helps. ryan - Original Message - From: "joe" <[EMAIL PROTECTED]> To: "MySQL" <[EMAIL PROTECTED]> Sent: Sunday, February 25, 2018 5:39 PM Subject: Random record Does anyone have any good ideas on how to pull a random record? I've been using PHP's random function to create a random number to pull, but the problem is that if I delete a record, it could still try to pull it. Is there a better way? I'd really like to be able to just pull it in the SQL statement instead of pulling a bunch of records then sorting throught that. Thanks, JOE - 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: Random record
Check the archives. The answer you seek is there. Cal http://www.calevans.com -Original Message- From: joe [mailto:[EMAIL PROTECTED]] Sent: Sunday, February 25, 2018 5:40 PM To: MySQL Subject: Random record Does anyone have any good ideas on how to pull a random record? I've been using PHP's random function to create a random number to pull, but the problem is that if I delete a record, it could still try to pull it. Is there a better way? I'd really like to be able to just pull it in the SQL statement instead of pulling a bunch of records then sorting throught that. Thanks, JOE - 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
Random record
Does anyone have any good ideas on how to pull a random record? I've been using PHP's random function to create a random number to pull, but the problem is that if I delete a record, it could still try to pull it. Is there a better way? I'd really like to be able to just pull it in the SQL statement instead of pulling a bunch of records then sorting throught that. Thanks, JOE
RE: Random record from a table
Check the archives, we had this discussion a few weeks ago and someone came up with a good solution. Cal http://www.calevans.com -Original Message- From: Sheni R. Meledath [mailto:[EMAIL PROTECTED]] Sent: Saturday, February 24, 2001 9:05 AM To: MySQL Masters Subject: Random record from a table Dear MySQL masters, Can I select a random record from a table. After doing a query on a table can i retrieve a record randomly. Is there a way in MySQL to achieve this. If, can you please send me details. thanks Sheni R Meledath [EMAIL PROTECTED] - 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
Random record from a table
Dear MySQL masters, Can I select a random record from a table. After doing a query on a table can i retrieve a record randomly. Is there a way in MySQL to achieve this. If, can you please send me details. thanks Sheni R Meledath [EMAIL PROTECTED] - 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: Selecting a random record
See section 7.39 of the manual. 'when' is a reserved word in 3.23. -- "There cannot be a crisis today. My schedule is already full." --Henry Kissinger > -Original Message- > From: Alan Halls [mailto:[EMAIL PROTECTED]] > Sent: Monday, January 15, 2001 15:15 > To: [EMAIL PROTECTED] > Subject: FW: Selecting a random record > > > Any idea why I get this error when I try to create this table > on a clean > 3.23.30 install on a BSDI box. > This was the output from a mysqldump from version 2.22.32 on a working > database. It will not allow me to recreate my database on the > new version. > > mysql> CREATE TABLE pp_additional_features ( > ->id int(11) DEFAULT '0' NOT NULL auto_increment, > ->profile_id varchar(11) DEFAULT '0' NOT NULL, > ->video char(3) NOT NULL, > ->audio char(3) NOT NULL, > ->slide_show char(3) NOT NULL, > ->toll_free char(3) NOT NULL, > ->scanning char(3) NOT NULL, > ->numpicts varchar(11) NOT NULL, > ->type_letter char(3) NOT NULL, > ->cards char(3) NOT NULL, > ->when datetime DEFAULT '-00-00 00:00:00' NOT NULL, > ->approved char(3) NOT NULL, > ->KEY id (id) > -> ); > ERROR 1064: You have an error in your SQL syntax near 'when > datetime DEFAULT > '-00-00 00:00:00' NOT NULL, >approved char(3) NOT NU' at line 12 - 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
FW: Selecting a random record
Any idea why I get this error when I try to create this table on a clean 3.23.30 install on a BSDI box. This was the output from a mysqldump from version 2.22.32 on a working database. It will not allow me to recreate my database on the new version. mysql> CREATE TABLE pp_additional_features ( ->id int(11) DEFAULT '0' NOT NULL auto_increment, ->profile_id varchar(11) DEFAULT '0' NOT NULL, ->video char(3) NOT NULL, ->audio char(3) NOT NULL, ->slide_show char(3) NOT NULL, ->toll_free char(3) NOT NULL, ->scanning char(3) NOT NULL, ->numpicts varchar(11) NOT NULL, ->type_letter char(3) NOT NULL, ->cards char(3) NOT NULL, ->when datetime DEFAULT '-00-00 00:00:00' NOT NULL, ->approved char(3) NOT NULL, ->KEY id (id) -> ); ERROR 1064: You have an error in your SQL syntax near 'when datetime DEFAULT '-00-00 00:00:00' NOT NULL, approved char(3) NOT NU' at line 12 Alan Halls Adoption.com - 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: Selecting a random record
To simplify
SELECT Field1, Field2, Field3 ORDER BY Rand() LIMIT 1;
At 10:01 AM 1/15/2001 -0700, Alan Halls wrote:
> On the site Adoption.com we have been using a mysql database to
> set up
>families who want to adopt. We have since redesigned our site and the old
>code is not working for us. I am trying to design a section of code in ASP
>to connect to the database and return a random record. Here is what I have
>so far:
>
> set SQL_Con = Server.CreateObject("ADODB.Connection")
> set SQL_Rec = Server.CreateObject("ADODB.Recordset")
> SQL_Con.OPEN application("conString2")
> SQL_Rec.ActiveConnection = SQL_Con
>
> SQL = "Select pp_search.profile_id, pp_search.thumbnail,
>pp_package.package_id, pp_package.profile_id from pp_search, pp_package
>where pp_package.package_id = 1 and pp_search.profile_id =
>pp_package.profile_id"
>
> All I can get is the first record in the page. If I go to the command
>prompt I get all matching records but don't know how to randomize what I
>display on the page.
>Can anyone help? I have been working futilly on this project :)
>
>Alan Halls
>Adoption.com
>
>
>-
>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
-
Scott Baker - Webster Internet - Network Technician
503.266.8253 - [EMAIL PROTECTED]
"Always bear in mind that your own resolution to success is more important
than any other one thing." - Abraham Lincoln
-
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
Selecting a random record
On the site Adoption.com we have been using a mysql database to set up
families who want to adopt. We have since redesigned our site and the old
code is not working for us. I am trying to design a section of code in ASP
to connect to the database and return a random record. Here is what I have
so far:
set SQL_Con = Server.CreateObject("ADODB.Connection")
set SQL_Rec = Server.CreateObject("ADODB.Recordset")
SQL_Con.OPEN application("conString2")
SQL_Rec.ActiveConnection = SQL_Con
SQL = "Select pp_search.profile_id, pp_search.thumbnail,
pp_package.package_id, pp_package.profile_id from pp_search, pp_package
where pp_package.package_id = 1 and pp_search.profile_id =
pp_package.profile_id"
All I can get is the first record in the page. If I go to the command
prompt I get all matching records but don't know how to randomize what I
display on the page.
Can anyone help? I have been working futilly on this project :)
Alan Halls
Adoption.com
-
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
