✨Make Ranking with MySQL

Version

select version();
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+

Function

ORDER BY

ROW_NUMBER() OVER (ORDER BY column DESC)  AS `ROW ASC`
RANK()       OVER (ORDER BY column DESC)  AS `RANK ASC`
DENSE_RANK() OVER (ORDER BY column DESC)  AS `DENSE ASC`

PARTITION BY

ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2 DESC)  AS `ROW ASC`
RANK()       OVER (PARTITION BY column1 ORDER BY column DESC)  AS `RANK ASC`
DENSE_RANK() OVER (PARTITION BY column1 ORDER BY column DESC)  AS `DENSE ASC`

Target Data

When you want to try a function, it's easier to create appropriate data with a with clause and try it.

SELECT 'fruit' 		AS category, 100 AS price, 'banana' 	AS food 
UNION ALL	SELECT 'fruit' 		AS category, 110 AS price, 'apple'  	AS food
UNION ALL	SELECT 'fruit' 		AS category, 120 AS price, 'lemon'  	AS food
UNION ALL	SELECT 'fruit' 		AS category, 120 AS price, 'apple'  	AS food
UNION ALL	SELECT 'fruit' 		AS category, 130 AS price, 'kiwi'   	AS food
UNION ALL	SELECT 'vegetable'  AS category, 100 AS price, 'cabbage' 	AS food 
UNION ALL	SELECT 'vegetable'  AS category, 110 AS price, 'cucumber'  	AS food
UNION ALL	SELECT 'vegetable'  AS category, 120 AS price, 'potato'  	AS food
UNION ALL	SELECT 'vegetable'  AS category, 120 AS price, 'onion'		AS food
UNION ALL	SELECT 'vegetable'  AS category, 130 AS price, 'tomato'   	AS food

Data

+-----------+-------+----------+
| category  | price | food     |
+-----------+-------+----------+
| fruit     |   100 | banana   |
| fruit     |   110 | apple    |
| fruit     |   120 | lemon    |
| fruit     |   120 | apple    |
| fruit     |   130 | kiwi     |
| vegetable |   100 | cabbage  |
| vegetable |   110 | cucumber |
| vegetable |   120 | potato   |
| vegetable |   120 | onion    |
| vegetable |   130 | tomato   |
+-----------+-------+----------+

ROW_NUMBER() OVER (ORDER BY column ASC), RANK(), DENSE_RANK()

ORDER BY column DESC

WITH items AS (
          SELECT 'fruit'      AS category, 100 AS price, 'banana'   AS food 
UNION ALL SELECT 'fruit'      AS category, 110 AS price, 'apple'    AS food
UNION ALL SELECT 'fruit'      AS category, 120 AS price, 'lemon'    AS food
UNION ALL SELECT 'fruit'      AS category, 120 AS price, 'apple'    AS food
UNION ALL SELECT 'fruit'      AS category, 130 AS price, 'kiwi'     AS food
UNION ALL SELECT 'vegetable'  AS category, 100 AS price, 'cabbage'  AS food 
UNION ALL SELECT 'vegetable'  AS category, 110 AS price, 'cucumber' AS food
UNION ALL SELECT 'vegetable'  AS category, 120 AS price, 'potato'   AS food
UNION ALL SELECT 'vegetable'  AS category, 120 AS price, 'onion'    AS food
UNION ALL SELECT 'vegetable'  AS category, 130 AS price, 'tomato'   AS food
)
SELECT
  category
  , food
  , price
  , ROW_NUMBER() OVER (ORDER BY price ASC)  AS `ROW ASC`
  , RANK()       OVER (ORDER BY price ASC)  AS `RANK ASC`
  , DENSE_RANK() OVER (ORDER BY price ASC)  AS `DENSE ASC`
FROM 
  items

Result

+-----------+----------+-------+---------+----------+-----------+
| category  | food     | price | ROW ASC | RANK ASC | DENSE ASC |
+-----------+----------+-------+---------+----------+-----------+
| fruit     | banana   |   100 |       1 |        1 |         1 |
| vegetable | cabbage  |   100 |       2 |        1 |         1 |
| fruit     | apple    |   110 |       3 |        3 |         2 |
| vegetable | cucumber |   110 |       4 |        3 |         2 |
| fruit     | lemon    |   120 |       5 |        5 |         3 |
| fruit     | apple    |   120 |       6 |        5 |         3 |
| vegetable | potato   |   120 |       7 |        5 |         3 |
| vegetable | onion    |   120 |       8 |        5 |         3 |
| fruit     | kiwi     |   130 |       9 |        9 |         4 |
| vegetable | tomato   |   130 |      10 |        9 |         4 |
+-----------+----------+-------+---------+----------+-----------+

ORDER BY column DESC

  , ROW_NUMBER() OVER (ORDER BY price DESC)  AS `ROW DESC`
  , RANK()       OVER (ORDER BY price DESC)  AS `RANK DESC`
  , DENSE_RANK() OVER (ORDER BY price DESC)  AS `DENSE DESC`

Result

+-----------+----------+-------+----------+-----------+------------+
| category  | food     | price | ROW DESC | RANK DESC | DENSE DESC |
+-----------+----------+-------+----------+-----------+------------+
| fruit     | kiwi     |   130 |        1 |         1 |          1 |
| vegetable | tomato   |   130 |        2 |         1 |          1 |
| fruit     | lemon    |   120 |        3 |         3 |          2 |
| fruit     | apple    |   120 |        4 |         3 |          2 |
| vegetable | potato   |   120 |        5 |         3 |          2 |
| vegetable | onion    |   120 |        6 |         3 |          2 |
| fruit     | apple    |   110 |        7 |         7 |          3 |
| vegetable | cucumber |   110 |        8 |         7 |          3 |
| fruit     | banana   |   100 |        9 |         9 |          4 |
| vegetable | cabbage  |   100 |       10 |         9 |          4 |
+-----------+----------+-------+----------+-----------+------------+

PARTITION BY column1 ORDER BY column2 DESC

use PARTITION BY when you want to create rankings by category.

ROW_NUMBER() OVER (PARTITION BY category ORDER BY price ASC)  AS `ROW ASC`
RANK()       OVER (PARTITION BY category ORDER BY price ASC)  AS `RANK ASC`
DENSE_RANK() OVER (PARTITION BY category ORDER BY price ASC)  AS `DENSE ASC`

Result

+-----------+----------+-------+---------+----------+-----------+
| category  | food     | price | ROW ASC | RANK ASC | DENSE ASC |
+-----------+----------+-------+---------+----------+-----------+
| fruit     | banana   |   100 |       1 |        1 |         1 |
| fruit     | apple    |   110 |       2 |        2 |         2 |
| fruit     | lemon    |   120 |       3 |        3 |         3 |
| fruit     | apple    |   120 |       4 |        3 |         3 |
| fruit     | kiwi     |   130 |       5 |        5 |         4 |
| vegetable | cabbage  |   100 |       1 |        1 |         1 |
| vegetable | cucumber |   110 |       2 |        2 |         2 |
| vegetable | potato   |   120 |       3 |        3 |         3 |
| vegetable | onion    |   120 |       4 |        3 |         3 |
| vegetable | tomato   |   130 |       5 |        5 |         4 |
+-----------+----------+-------+---------+----------+-----------+

Supplement

e.g. 1

SELECT
  category
  , food
  , price
  , (
    SELECT
      COUNT(i2.price)
    FROM 
      items AS i2
    WHERE 
      i1.price < i2.price
    ) + 1 AS `rank`
FROM 
  items AS i1
ORDER BY 
  `rank`

Result

+-----------+----------+-------+------+
| category  | food     | price | rank |
+-----------+----------+-------+------+
| fruit     | kiwi     |   130 |    1 |
| vegetable | tomato   |   130 |    1 |
| fruit     | lemon    |   120 |    3 |
| fruit     | apple    |   120 |    3 |
| vegetable | potato   |   120 |    3 |
| vegetable | onion    |   120 |    3 |
| fruit     | apple    |   110 |    7 |
| vegetable | cucumber |   110 |    7 |
| fruit     | banana   |   100 |    9 |
| vegetable | cabbage  |   100 |    9 |