Mysql – Get the rank of a fruit after ordering

MySQLrank

I have the following table, called sales

+--------+--------+
| fruit  | amount |
+--------+--------+
| cherry |    124 |
| plum   |     23 |
| pear   |     19 |
| apple  |     13 |
| banana |      4 |
| orange |      2 |
+--------+--------+

And using this to get the rank

SET @rank=0; 
SELECT @rank:=@rank+1 AS rank, fruit, amount FROM sales ORDER BY amount DESC;

+------+--------+--------+
| rank | fruit  | amount |
+------+--------+--------+
|    1 | cherry |    124 |
|    2 | plum   |     23 |
|    3 | pear   |     19 |
|    4 | apple  |     13 |
|    5 | banana |      4 |
|    6 | orange |      2 |
+------+--------+--------+

So now I want to get the rank of apple but I couldn't figure out how exactly to do it.

Best Answer

You can place your query inside a subquery and do a WHERE against it

PROPOSED QUERY

SET @rank=0; 
SELECT rank FROM
(SELECT @rank:=@rank+1 AS rank, fruit, amount FROM sales ORDER BY amount DESC) A
WHERE fruit='apple';

SAMPLE DATA

DROP DATABASE IF EXISTS Alw7SHxD;
CREATE DATABASE Alw7SHxD;
USE Alw7SHxD
CREATE TABLE sales
(
    id INT NOT NULL AUTO_INCREMENT,
    fruit VARCHAR(20),
    amount INT DEFAULT 0,
    PRIMARY KEY (id)
);
INSERT INTO sales (fruit,amount) VALUES
('cherry',124), ('plum'  , 23), ('pear'  , 19),
('apple' , 13), ('banana',  4), ('orange',  2);
SELECT * FROM sales;
SET @rank=0; 
SELECT rank FROM
(SELECT @rank:=@rank+1 AS rank, fruit, amount FROM sales ORDER BY amount DESC) A
WHERE fruit='apple';

SAMPLE CODE EXECUTED

mysql> DROP DATABASE IF EXISTS Alw7SHxD;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE DATABASE Alw7SHxD;
Query OK, 1 row affected (0.00 sec)

mysql> USE Alw7SHxD
Database changed
mysql> CREATE TABLE sales
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     fruit VARCHAR(20),
    ->     amount INT DEFAULT 0,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO sales (fruit,amount) VALUES
    -> ('cherry',124), ('plum'  , 23), ('pear'  , 19),
    -> ('apple' , 13), ('banana',  4), ('orange',  2);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM sales;
+----+--------+--------+
| id | fruit  | amount |
+----+--------+--------+
|  1 | cherry |    124 |
|  2 | plum   |     23 |
|  3 | pear   |     19 |
|  4 | apple  |     13 |
|  5 | banana |      4 |
|  6 | orange |      2 |
+----+--------+--------+
6 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SET @rank=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT rank FROM
    -> (SELECT @rank:=@rank+1 AS rank, fruit, amount FROM sales ORDER BY amount DESC) A
    -> WHERE fruit='apple';
+------+
| rank |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

Related Solutions

Mysql – Sorting the Table and getting the position

Analytic functions have not been implemented in MySQL yet. There are some ways to overcome this limitation.

Joining the table to itself using not equality but > or >= and then using GROUP BY and COUNT(*) (what @deszo is essentially doing - a self LEFT JOIN). Downside is that it may not be fast enough, especially if you are self-joining a complex query/view.

Using MySQL variables (I should add a huge Warning though: using MySQL variables this way may be broken in future MySQL releases. This is not a guaranteed behaviour):

SELECT LastName
     , FirstName
     , RowNumber
     , Rank
     , DenseRank
     , RowNumber_OverPartitionBy
FROM
  ( SELECT p.*,
           @rown := @rown + 1 AS RowNumber
         , @rnk :=  CASE WHEN LastName = @prev_lastname
                      THEN @rnk
                      ELSE @rown 
                    END AS Rank
         , @drnk := CASE WHEN LastName = @prev_lastname
                      THEN @drnk
                      ELSE @drnk + 1 
                    END AS DenseRank
         , @rowp := CASE WHEN LastName = @prev_lastname
                      THEN @rowp + 1
                      ELSE 1 
                    END AS RowNumber_OverPartitionBy
         , @prev_lastname := LastName
    FROM 
        Person p 
      CROSS JOIN
        ( SELECT @rown := 0, @rnk := 0
               , @drnk := 0, @rowp := 0
        ) AS dummy 
    ORDER BY LastName
           , FirstName            
  ) AS p 
ORDER BY LastName
       , FirstName ;

You can test in SQL-Fiddle: test-1

In other DBMS, that have window (analytic) functions, you could have the same in a much more compact query:

SELECT LastName
     , FirstName
     , Row_Number() OVER(ORDER BY LastName, FirstName)
         AS RowNumber
     , Rank() OVER(ORDER BY LastName)
         AS Rank
     , Dense_Rank() OVER(ORDER BY LastName)
         AS DenseRank
     , Row_Number() OVER( PARTITION BY LastName
                          ORDER BY FirstName)
         AS RowNumber_OverPartitionBy
FROM
    Person p 
ORDER BY 
    LastName
  , FirstName ;

Test in SQL-Fiddle (SQL-Server, Postgres, Oracle): test-2

Sql-server – Best way to rank all the columns in a table and store those ranks in another table

You would need an index on each column in order to avoid a sort of every row and column during the ranking process. That would of course introduce significant overhead as scores are updated continuously. Probably not an option unless you have a high-end hardware configuration.

The ranking processes could be offloaded onto a read-only copy maintained via log shipping on a different box. That would avoid concurrency issues with hitting the live database system of record and provide more server resources for both the intensive batch process and live database. If you are running Enterprise Edition, you could use a database snapshot on the same box as the source if your hardware is sufficiently sized.

You mention truncate and insert. Just want to mention that you could also use SWITCH such that you truncate and insert into a staging table. Once the staging load is completed, truncate Table_Ranks and then SWITCH from the staging table into Table_Ranks in a single transaction. That way, Table_Ranks would never be empty.