mysql - SQL ranking solution -

I am implementing the ranking solution for one of my table so that get rid of expensive questions that are COUNT (* ), Range and offset clause my problem is that I do not know why the calculation of the position is incorrect. Please see your examples to redo the problem.

  create table `acl` (` id` MEDIUMINT (8) unsigned NULL AUTO_INCREMENT, `name` VARCHAR (32) No NULL,` limiter` INTEGER (11) signature tap, primary key (`Id`)) engine = INNODB; Create table 'quote` (`id` MEDIUMINT (8) unsigned NULL AUTO_INCREMENT,` created_at` integer (11) unsigned NULL, `integer (11) reputation` not NULL, primary key signed (` id`)) Engine = InnoDB; Enter value in 'ACL' (`name`,` limiter`) ('user', 0), ('staff', blank); Value in `bid '(` created_at', 'reputation') values ​​(UNIX_TIMESTAMP (), 0), (UNIX_TIMESTAMP () + 1, 0); SET @acl_id: = 0, @ position: = 0; Select acl.id as acl_id, quote_id as quote.id, the largest (@position: = IF (@acl_id = acl.id, @position + 1, 1), at least (0, @ Acl_id: = acl.id)) as the position quotation from ACL (acl.limiter faucet or quote.reputation & gt; = acl.limiter) by order acl.id ASC, DESC quote.created_at;  

Let me select all the ALL rows to connect them and at the same time connect with the bidding rows, set their position, but whatever I get is every Position = 1 for the line. Someone suggested to move the variable assignment to join me, but the problem is that I have a question ... How to appoint a post in a single question?

A little modified query from the article in my blog:

& nbsp;

  SELECT q *, From @r: = @r + 1 (@_acl_id, select: = -1, @r: = 0) Select wars STRAIGHT_JOIN (acl_id), select ACcl from quote_id quote.id from ACL JOIN (acl.limiter faucet or quote.reputation & gt; = ACL .limiter) by command acl.id asc, quote.created_at DESC) Where is q @_acl_id & lt; & Gt; Acl_id then @r: not NULL  

: (= acl_id @_acl_id) = 0 and the end of 0 is zero and