1

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.

  • See also the "Windowing functions" of MariaDB. – Rick James Aug 16 '17 at 14:09
0

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 !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.