CODEWITHSUNDEEP
mysqlsqlranking
João
João

Reputation: 120

MySQL query to dynamic "Ranking rows"

I'm having problems running a query ranking. The inner SELECT gives the rows in order of ranking, for each line, the variable @rank increases, if not a position equal to the previous ranking. But the @rank is not really the correct position.

I'm trying to do a ranking grouped and ordered by those with the highest value.

SET @prev := NULL;
SET @curr := NULL;
SET @rank := 0;
SELECT
    @prev := @curr,
    @curr := SUM( a.value ) AS SUM_VALUES,
    @rank := IF(@prev = @curr, @rank, @rank+1) AS rank,
    b.id AS b_id,
    b.name AS b_nome

FROM
    a INNER JOIN b ON ( a.b_id = b.id )

GROUP BY b.id
ORDER BY SUM_VALUES DESC;

Result:

----------------------------------------------------
@prev := @curr | SUM_VALUES | rank | b_id  | b_nome
---------------|------------|------|-------|--------
NULL           | 10         | 2    | 2     | BBB
NULL           | 2          | 1    | 1     | AAA

Here BBB was to return in the first place in the ranking and AAA, second in ranking. But this does not occur, one idea of what is happening?

A test dump

CREATE TABLE `a` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `b_id` INT(10) NULL DEFAULT NULL,
    `value` INT(10) NULL DEFAULT NULL,
    `name` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `b_id` (`b_id`),
    CONSTRAINT `fk_b` FOREIGN KEY (`b_id`) REFERENCES `b` (`id`)
)
ENGINE=InnoDB;

CREATE TABLE `b` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

INSERT INTO `b` (`id`, `name`) VALUES (1, 'AAA');
INSERT INTO `b` (`id`, `name`) VALUES (2, 'BBB');

INSERT INTO `a` (`id`, `b_id`, `value`, `name`) VALUES (1, 1, 2, 'smaller');
INSERT INTO `a` (`id`, `b_id`, `value`, `name`) VALUES (2, 2, 10, 'bigger');

Upvotes: 2

Views: 1744

Answers (2)

Johan
Johan

Reputation: 76597

having
It will be slow, but a having clause will run after all the selects, joins, where and group by's have finished and are fully resolved.
The only problem is that having does not use an index, whilst where does use an index. 

SELECT
  ranking stuff
FROM 
  lot of tables
WHERE simple_condition
HAVING filters_that_run_last

Make your joins explicit
Note that you don't have to mix explicit and implicit joins.
If you want a cross join, you can use the cross join keyword. 

    ....
    ) AS Ranking
    CROSS JOIN (SELECT @curr := null, @prev := null, @rank := 0) InitVars
WHERE
  Ranking.regional_id = 1003

Upvotes: 3

João
João

Reputation: 120

First, Thank you all!

I found a way to return the expected result making other selects 2

A) Select first grouped and ordered

SELECT
    SUM( a.value ) AS SUM_VALUES,
    b.id AS b_id,
    b.name AS b_name
FROM
    a INNER JOIN b ON ( a.b_id = b.id )
GROUP BY b.id
ORDER BY SUM_VALUES DESC

B) I do this ranking list

SELECT
    R.*,
   @prev := @curr,
   @curr := R.SUM_VALUES,
   @rank := IF(@prev = @curr, @rank, @rank+1) AS rank
FROM (
        SELECT
            SUM( a.value ) AS SUM_VALUES,
            b.id AS b_id,
            b.name AS b_name
        FROM
            a INNER JOIN b ON ( a.b_id = b.id )
        GROUP BY b.id
        ORDER BY SUM_VALUES DESC
) AS R

C) And lastly, just select what matters

SELECT
    Ranking.b_id,
    Ranking.b_name,
    Ranking.rank
FROM
(
    SELECT
        R.*,
       @prev := @curr,
       @curr := R.SUM_VALUES,
       @rank := IF(@prev = @curr, @rank, @rank+1) AS rank
    FROM (
            SELECT
                SUM( a.value ) AS SUM_VALUES,
                b.id AS b_id,
                b.name AS b_name
            FROM
                a INNER JOIN b ON ( a.b_id = b.id )
            GROUP BY b.id
            ORDER BY SUM_VALUES DESC
    ) AS R
) AS Ranking
WHERE
    Ranking.b_id = 1

The result of this query was:

+------+--------+------+
| b_id | b_name | rank |
+------+--------+------+
|    1 | AAA    |    2 |
+------+--------+------+

Upvotes: 2

Related Questions