List:General Discussion« Previous MessageNext Message »
From:Gleb Paharenko Date:July 21 2005 9:23am
Subject:Re: random rows selection
View as plain text  
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 \@rc = 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 <michael@stripped> 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
 / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
       <___/   www.mysql.com



Thread
random rows selectionMichael Monashev20 Jul
  • Re: random rows selectionGleb Paharenko20 Jul
    • Re: random rows selectionMichael Monashev20 Jul
      • Re: random rows selectionGleb Paharenko21 Jul
  • Re: random rows selectionChris Knipe20 Jul
  • Re: random rows selectionNuno Pereira21 Jul