Always use strict; use warnings; at the beginning of your perl code. It catches common mistakes, including the one you are making.

The problem is that Perl replaces variable names in double-quoted strings by their value (we call that "interpolation"), and @rownum happens to look like a variable name in Perl. So use single-quote strings instead:

$sth = $dbh->prepare('Select @rownum:=@rownum+1 rank, p.* from player 
+p, (SELECT @rownum:=0) r order by score desc limit 10'); 
[download]

Hi Thanks that worked ...but what if I need to use a variable in limit e.g

my $temp = 50;

$sth = $dbh->prepare('Select @rownum:=@rownum+1 rank, p.* from player +p, (SELECT @rownum:=0) r order by score desc limit $temp');

You use placeholders:

$sth = $dbh->prepare('Select @rownum:=@rownum+1 rank, p.* from player 
++p, (SELECT @rownum:=0) r order by score desc limit ?');
# and later
$sth->execute($temp);
[download]

This is generally safer than interpolating variables into SQL strings, and prevents SQL injection attacks.

See also: DBI, and perlop for the rules about string interpolation and escaping.

Perl 6 - the future is here, just unevenly distributed

you can also use backslash

$sth = $dbh->prepare("Select \@rownum:=\@rownum+1 rank, p.* from playe
+r +p, (SELECT \@rownum:=0) r order by score desc limit $temp"); 
[download]

but it looks a little oddly