CODEWITHSUNDEEP
mysqlperldbi
user2020692
user2020692

Reputation: 53

mysql query has different result executed in perl and mysql

I try to follow this thread SQL Limit results per column value to show only top 10 per branch based on amount but I have trouble in writing the query in Perl.

If I run the query in MYSQL Front, it results top 10 per branch based on amount. But when I put the query in Perl with DBI module, it results all rows. Is there anything wrong in my Perl Script?

The Mysql query :

select name, branch, amount from (select name, branch, amount, if(@mainbranch = branch, @num := @num + 1, (@num := 0 || @mainbranch := branch)) as num from branch_amount where branch in (102, 106, 0 ) order by branch, amount desc ) a where num<=10

The Perl Script :

use DBI;
my $db=DBI->connect("DBI:mysql:$database;host=$host;user=$user;password=$passwd")
my $query = "select name, branch, amount from (select name, branch, amount, if(\@mainbranch = branch, \@num := \@num + 1, (\@num := 0 || \@mainbranch := branch)) as num from branch_amount where branch in (102, 106, 0 ) order by branch, amount desc ) a where num<=10";
$db->prepare("$query");
$sth->execute() or die "$query";

I try to figure the result from Perl Script, and the num field value is 1 for all rows. I suggest that operation within if does not work, but i dont know why and how. All help would be appreciated

Upvotes: 1

Views: 206

Answers (1)

ysth
ysth

Reputation: 98388

Does putting this before your prepare help?

$db->do('set @num=-1');
$db->do('set @mainbranch=-1');

Upvotes: 2

Related Questions