Privet!
> But this query can return empty results, if we use it with WHERE:
Yes, it can. However, we're able to change the technique and obtain
one random record with WHERE condition in query. With several iterations
it is possible to get several random records.
First we should get the count of records which satisfy our condition.
Then we're making a random number which is less than obtained before
count. And at the end we perform a query with WHERE condition where
we're sorting records by auto_increment field, limiting the number of
rows with that random value and the last row is taken. This record is a
random record from a set that satisfies WHERE condition. A bit cleaner
code is required when we're generating a random number (zero shouldn't be
in possible results). Here is a simple program which illustrates this concept.
#!/usr/bin/perl
#
use strict;
use DBI;
use User::pwent;
use POSIX;
my ($dbh,$sth,$sql_fmt,$sql,@row,@frow,$user,$rval,$rcount);
$dbh = DBI->connect
("DBI:mysql:test:127.0.0.1:3731",
"root","",
{RaiseError => 1})
or die "connecting : $DBI::errstr\n";
$dbh->do("DROP TABLE IF EXISTS ttable");
$dbh->do("CREATE TABLE ttable(id INT auto_increment, col1 CHAR(8), col2 char(8),
col3 char(8), primary key (id))");
for(my $i=0;$i<2000;$i++)
{
$rval = ceil( rand 200 );
$sql = "insert into ttable(col1,col2,col3)
values('$rval','$rval','$rval')
";
$dbh->do($sql);
}
$sth = $dbh->prepare("SELECT count(id) FROM ttable where col3 = 50");
$sth->execute;
while((@row) = $sth->fetchrow_array () ) {
print $row[0]."\n";
$rcount = $row[0];
}
$sth->finish;
$rval = ceil (rand $rcount);
print "rval = $rval \n";
$dbh->do(" SET [EMAIL PROTECTED] = 0");
#50 - just a number for WHERE condtition
$sth = $dbh->prepare("SELECT id,col1,col2,col3 FROM ttable where col3 = 50
order by id asc limit $rval");
$sth->execute;
while(@row = $sth->fetchrow_array () ) {
@frow = @row;
}
#now we're getting the last row
print $frow[0]." ".$frow[1]." ".$frow[2]." ".$frow[3]."\n";
$sth->finish;
exit;
$dbh->do("DROP TABLE ttable");
$dbh->disconnect;
Michael Monashev <[EMAIL PROTECTED]> wrote:
> Hello
>
> GP> Similar questions have been asked before. For example:
> GP> http://lists.mysql.com/mysql/184088
>
> Thank you. I found something interesting:
>
> SELECT @rand_id:= CAST( 1 + MAX(id)*RAND() AS UNSIGNED) FROM history;
> SELECT * FROM history WHERE id >= @rand_id LIMIT 1;
>
> But this query can return empty results, if we use it with WHERE:
>
> SELECT @rand_id:= CAST( 1 + MAX(id)*RAND() AS UNSIGNED) FROM history;
> SELECT col1, col2 FROM table WHERE id >= @rand_id AND col3=123 LIMIT 5;
> ^^^^^^^^^^^^
> :-(
>
> Sincerely,
> Michael,
> http://xoib.com/ http://3d2f.com/
> http://qaix.com/ http://ryxi.com/
> http://gyxe.com/ http://gyxu.com/
> http://xywe.com/ http://xyqe.com/
>
>
>
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
