Get the rank of a fruit after ordering 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.
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&gt; DROP DATABASE IF EXISTS Alw7SHxD;
Query OK, 1 row affected (0.01 sec)

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

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

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

mysql&gt; 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&gt;


PROPOSED QUERY EXECUTED

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

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

mysql&gt;


GIVE IT A TRY !!!