mysql query has different result executed in perl and mysql

Question

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

ysth · Accepted Answer · 2013-09-30 03:44:13Z

2

Does putting this before your prepare help?

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

answered Sep 30, 2013 at 3:44

ysth

99.1k6 gold badges126 silver badges220 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql query has different result executed in perl and mysql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related