SQL ranking solution

Question

Hello, I am implementing ranking solution for one of my tables to optimize read queries to get rid of expensive queries which use COUNT(*), LIMIT and OFFSET clause. My problem is that I don't know why position calculation are incorrect. Please look at my example to reproduce problem.

CREATE TABLE `acl`
(
    `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(32) NOT NULL,
    `limiter` INTEGER(11) SIGNED NULL,
    PRIMARY KEY (`id`)
)
ENGINE=INNODB;

CREATE TABLE `quote`
(
    `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    `created_at` INTEGER(11) UNSIGNED NOT NULL,
    `reputation` INTEGER(11) SIGNED NOT NULL,
    PRIMARY KEY (`id`)
)
ENGINE=INNODB;

INSERT INTO `acl` (`name`, `limiter`) VALUES ('Users', 0), ('Staff', null);
INSERT INTO `quote` (`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, 
  GREATEST(@position := IF(@acl_id = acl.id, @position + 1, 1), 
    LEAST(0, @acl_id := acl.id)) AS position 
FROM acl JOIN quote 
  ON (acl.limiter IS NULL OR quote.reputation >= acl.limiter) 
ORDER BY acl.id ASC, quote.created_at DESC;

I would like that select query to fetch all acl rows and join them with quote rows at the same time, set their position, but all I get is position=1 for every row. Someone suggested me to move variable assignments to JOIN or ORDER clause but the problem remains. My question is... how to assign position in single query?

Answer 1

You're using too much magic, and trying to use a SQL statement like a conventional loop-based programming language. You need to think of SQL as a declarative language. The rows are evaluated on their own, not relative to which row was evaluated previously.

(There are some cases when you can cause special effects with user variables, but you need to use this technique with great care. It's easy to get it wrong, because you need to understand the order of evaluating rows, which may not be the order you specify in the ORDER BY clause.)

Thanks for including complete SQL that shows your table structure and sample data. You've shown an SQL query that fails to do what you want, and you say you want some ranking solution, but ranking by what criteria? What order do you want the rows to be in, and what subset of rows do you want? You need to be clear about your goal when you ask questions like this.

Please edit your question above and add some more information. A example of the desired output would be best. If you can do that I'll check back and try to help further.

PS: I know you can't leave comments until you have 50 reputation points. Try to edit your original question.

Answer 2

A slightly modified query from the article in my blog:

• Analytic functions: SUM, AVG, ROW_NUMBER

 

SELECT  q.*,
        @r := @r + 1
FROM    (
        SELECT  @_acl_id := -1,
                @r := 0
        ) vars
STRAIGHT_JOIN
        (
        SELECT  acl.id AS acl_id, quote.id AS quote_id
        FROM    acl
        JOIN    quote
        ON      (acl.limiter IS NULL OR quote.reputation >= acl.limiter)
        ORDER BY
                acl.id ASC, quote.created_at DESC
        ) q
WHERE   CASE WHEN @_acl_id <> acl_id THEN @r := 0 ELSE 0 END IS NOT NULL
        AND (@_acl_id := acl_id) IS NOT NULL