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.

So I think you have achieved upto distance calculation, right? Now you want to achieve rank within a item group based on distance? let me know if my interpretation is correct — Dipendu Paul, Mar 04 '14 at 07:37
Yes, the distance is already calculated. I just need to rank it based on which is nearest. — KaHeL, Mar 04 '14 at 07:50

Hamidreza · Accepted Answer · 2014-03-04T10:26:56.217

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

It works but there is something strange on the ranking on distance. One distance is 0, 22.09 and 3.20 but the result is 2,1,3 which is not correct. Happens most of the time is the distance is 0. — KaHeL, Mar 04 '14 at 09:26

score 0 · Answer 2 · answered Mar 04 '14 at 08:10

Here is a solution which achieves the final resultset in your description:

  SELECT a.idproduct, a.common_name, a.distance FROM
  (
      SELECT (@rownumber1:= @rownumber1 + 1) AS rn, dt.*
      FROM distance_table dt,(SELECT @rownumber1:= 0) nums
      ORDER BY common_name, distance 
  ) a  
  JOIN
  ( 
     SELECT MIN(rn) AS minRn, common_name FROM  
     (
         SELECT (@rownumber:= @rownumber + 1) AS rn, dt.*
         FROM distance_table dt,(SELECT @rownumber:= 0) nums
         ORDER BY common_name, distance 
     ) c 
     GROUP BY common_name
   ) b
   ON a.common_name = b.common_name
   AND a.rn = b.minRn

Here is the code at SQL Fiddle

I have assumed that distance table is already calculated, so in the above query wherever distance_table is mentioned, it can be replaced with query which gives distance resultset as output.

Here is the query for rank within each group:

  SELECT a.idproduct, a.common_name, a.distance, (a.rn - b.minRn + 1) AS rank FROM
  (
      SELECT (@rownumber1:= @rownumber1 + 1) AS rn, dt.*
      FROM distance_table dt,(SELECT @rownumber1:= 0) nums
      ORDER BY common_name, distance 
  ) a  
  JOIN
  ( 
     SELECT MIN(rn) AS minRn, common_name FROM  
     (
         SELECT (@rownumber:= @rownumber + 1) AS rn, dt.*
         FROM distance_table dt,(SELECT @rownumber:= 0) nums
         ORDER BY common_name, distance 
     ) c 
     GROUP BY common_name
   ) b
   ON a.common_name = b.common_name

Here is the code at SQL Fiddle Let me know if it solves your problem.

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

2 Answers2

SQL Fiddle