Get ranking with only 1 MySQL Query

Sometime, you need to order and get member ranking base on one or more condition, you can get all information and use a array to get need ranking, but today I will introduce to you a better way, and certainly – faster way to get it: using just 1 query to get member ranking.

This is query in query command:

SET @rownum := 0; #Create counter variable
SELECT *
FROM
(
SELECT DISTINCT(FieldID), @rownum := @rownum + 1 as ranking
FROM TableName.FieldName
WHERE
FieldID = '27'
) AS NewTable
WHERE NewTable.FieldID = '00022'


This is perl code for the above query:

my $sql0 =" SET \@rownum := 0;";
my $sql1=" SELECT ranking FROM ..... LIMIT 1;";

$db->do($sql0);
$sth=$db->prepare($sql1);

$row=$sth->execute;

if(defined($row) && $row == 1)
{
$rec = $sth->fetchrow_arrayref;
$result = $rec->[0];
}
$sth->finish;