Rank column based on a specific column with same row values for MySQL

Question

I wanted to create something that will rank the distance on which is nearest to the user via a query. Now the only problem is that I'm not sure how to implement it for MySQL. I'm thinking of something like Rank partition by implementation in Oracle. For now here is my query:

SELECT  p.idproduct,
p.common_name,
ROUND(
SQRT(
    POW(69.1 * (s.store_lat - 4.946966), 2) +
    POW(69.1 * (114.960770 - s.store_long) * COS(s.store_lat / 57.3), 2)),2) AS distance
FROM    product p
        INNER JOIN branches b
            ON b.idproduct = p.idproduct
        INNER JOIN store s
            ON b.idstore = s.idstore
        INNER JOIN
        (   SELECT DISTINCT p.common_name
            FROM    shopping_list_content s
                    INNER JOIN product p
                        ON s.iditem = p.idproduct
            WHERE   s.idlist =64
        ) s
            ON s.common_name = p.common_name

Now it has a result like:

idproduct | common_name | distance
1         | item 1      |   0
1         | item 1      |   1
2         | item 2      |   3
2         | item 2      |   1
3         | item 3      |   2
3         | item 3      |   0

and added a rank I'm supposed to get:

idproduct | common_name | distance | rank
1         | item 1      |   0      | 1
1         | item 1      |   1      | 2
2         | item 2      |   3      | 2
2         | item 2      |   1      | 1
3         | item 3      |   2      | 2
3         | item 3      |   0      | 1

and finally via nested select I will get:

idproduct | common_name | distance | rank
1         | item 1      |   0      | 1
2         | item 2      |   1      | 1
3         | item 3      |   0      | 1

I've seen something like @curRank here (Rank function in MySQL) but not sure on how I should implement it based on my current query.

I tried using a GROUP BY for the common_name column but I guess this is totally not the right way to do this. Hope someone can help.

Answer 1

This query work fine in MySQL for ranking:

SELECT TAB1.idproduct,TAB1.common_name,TAB1.distance,
(TAB1.RN - TAB2.MN) + 1 RANK FROM
(SELECT T1.*,@ROWNUM := @ROWNUM + 1 RN FROM
(SELECT * FROM (SELECT  p.idproduct,
p.common_name,
ROUND(
SQRT(
POW(69.1 * (s.store_lat - 4.946966), 2) +
POW(69.1 * (114.960770 - s.store_long) * COS(s.store_lat / 57.3), 2)),2) AS distance
FROM    product p
    INNER JOIN branches b
        ON b.idproduct = p.idproduct
    INNER JOIN store s
        ON b.idstore = s.idstore
    INNER JOIN
    (   SELECT DISTINCT p.common_name
        FROM    shopping_list_content s
                INNER JOIN product p
                    ON s.iditem = p.idproduct
        WHERE   s.idlist =64
    ) s
        ON s.common_name = p.common_name)TABLE1 
ORDER BY idproduct,common_name,distance)T1,
(SELECT @ROWNUM := 0) RN)TAB1
INNER JOIN
(SELECT T2.*,MIN(RN) MN FROM
(SELECT T1.*,@ROWNUM := @ROWNUM + 1 RN FROM
(SELECT * FROM (SELECT  p.idproduct,
p.common_name,
ROUND(
SQRT(
POW(69.1 * (s.store_lat - 4.946966), 2) +
POW(69.1 * (114.960770 - s.store_long) * COS(s.store_lat / 57.3), 2)),2) AS distance
FROM    product p
    INNER JOIN branches b
        ON b.idproduct = p.idproduct
    INNER JOIN store s
        ON b.idstore = s.idstore
    INNER JOIN
    (   SELECT DISTINCT p.common_name
        FROM    shopping_list_content s
                INNER JOIN product p
                    ON s.iditem = p.idproduct
        WHERE   s.idlist =64
    ) s
        ON s.common_name = p.common_name)TABLE1 
ORDER BY idproduct,common_name,distance)T1,
(SELECT @ROWNUM := 0) RN)T2
GROUP BY idproduct,common_name)TAB2
ON TAB1.idproduct = TAB2.idproduct AND
TAB1.common_name = TAB2.common_name;

SQL Fiddle