rank.sql

CREATE TABLE `standing` (
	`team` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`pts` INT(11) NULL DEFAULT NULL,
	`season` INT(11) NULL DEFAULT NULL
)
COLLATE='utf8mb4_general_ci'
ENGINE=MyISAM
;
  
  
  SELECT
    team,
    season,
    pts,
    RANK() OVER (PARTITION BY season ORDER BY pts DESC) AS rnk,
    LAG(rank_prev_season) OVER (PARTITION BY team ORDER BY season) AS prev_rnk
FROM
    (
        SELECT
            team,
            season,
            pts,
            RANK() OVER (PARTITION BY season ORDER BY pts DESC) AS rank_prev_season
        FROM
            standing
    ) AS subquery
ORDER BY
    season, rnk;



+--------+--------+------+-----+----------+
| team   | season | pts  | rnk | prev_rnk |
+--------+--------+------+-----+----------+
| Plzen  |   2021 |   60 |   1 |     NULL |
| Sparta |   2021 |   55 |   2 |     NULL |
| Slavia |   2021 |   40 |   3 |     NULL |
| Sparta |   2022 |   54 |   1 |        2 |
| Slavia |   2022 |   50 |   2 |        3 |
| Plzen  |   2022 |   48 |   3 |        1 |
| Slavia |   2023 |   55 |   1 |        2 |
| Plzen  |   2023 |   49 |   2 |        3 |
| Sparta |   2023 |   38 |   3 |        1 |
+--------+--------+------+-----+----------+