Get Only Registry Ranking Number in One Query

Asked

Viewed 25 times

0

There’s a way I can get the ranking of a record in the table only with a Query (even if I have a Subquery) ?

For example, I did it 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

It’s like I only get the ID rank I want ?

I tried to do the WHERE, but then it shows the rank as 1.

mysql

1 answer

1


Do a SELECT that picks up this result and then you do the WHERE, that way:

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'

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.