Get Only a Query Registry Ranking Number

0

How can I get the Ranking of a record in the table with just a Query (even if it has a Subquery)?

For example, I did this below:

SELECT
    a.id_concessionaria,
    a.avscore,
    @rank := @rank + 1 AS ranking
FROM
    (
        SELECT
            id_concessionaria,
            Avg(rating) AS AvScore
        FROM
            dealer_ratings
        GROUP BY
            id_concessionaria
    ) a
ORDER BY
    a.avscore DESC

And returns:

12286   5.0000  1
11393   5.0000  2
11784   5.0000  3
11816   5.0000  4
12291   4.3333  5 --------------------- Essa aqui
12634   4.0000  6
19021   3.0000  7
10194   2.0000  8

How can I get just the ID rank I want?

I tried to WHERE , but then it shows Rank as 1.

    
asked by anonymous 27.07.2016 / 15:28

1 answer

1

Make a SELECT that takes this result and from there you do WHERE, like this:

SELECT b.ranking FROM (
    SELECT
        a.id_concessionaria,
        a.avscore,
        @rank := @rank + 1 AS ranking
    FROM
        (
            SELECT
                id_concessionaria,
                Avg(rating) AS AvScore
            FROM
                dealer_ratings
            GROUP BY
                id_concessionaria
        ) a
    ORDER BY
        a.avscore DESC
) AS b WHERE b.id_concessionaria = '12291'
    
27.07.2016 / 15:50