MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT VERSION();


VERSION()


8.0.26


DROP TABLE IF EXISTS `olimpicos`;

CREATE TABLE IF NOT EXISTS `olimpicos` (
  `pais` VARCHAR(20),
  `oro` SMALLINT UNSIGNED,
  `plata` SMALLINT UNSIGNED,
  `bronce` SMALLINT UNSIGNED,
  `total` SMALLINT UNSIGNED AS (`oro` + `plata` + `bronce`)
);

INSERT INTO `olimpicos`
  (`pais`, `oro`, `plata`, `bronce`)
VALUES
  ('pais 1', 0, 1, 0),
  ('pais 2', 1, 2, 3),
  ('pais 3', 1, 2, 3),
  ('pais 4', 5, 2, 1);

SELECT
  DENSE_RANK() OVER (
    ORDER BY
      `oro` desc,
      `plata` desc,
      `bronce` desc
  ) `posicion`,
  `pais`,
  `oro`,
  `plata`,
  `bronce`,
  `total`
FROM
  `olimpicos`;


posicion
pais
oro
plata
bronce
total


1
pais 4
5
2
1
8

2
pais 2
1
2
3
6

2
pais 3
1
2
3
6

3
pais 1
0
1
0
1


WITH `medallero_olimpico` AS (
  SELECT
    DENSE_RANK() OVER (
      ORDER BY
        `oro` desc,
        `plata` desc,
        `bronce` desc
    ) `posicion`,
    `pais`,
    `oro`,
    `plata`,
    `bronce`,
    `total`
  FROM
    `olimpicos`
), `pais_a_buscar` AS (
  SELECT
    `posicion`,
    `pais`
  FROM
    `medallero_olimpico`
  WHERE
    `pais` = 'pais 1'
)
SELECT
  `pais_a_buscar`.`pais`,
  `medallero_olimpico`.`oro`,
  `medallero_olimpico`.`plata`,
  `medallero_olimpico`.`bronce`,
  `medallero_olimpico`.`total`
FROM
  `medallero_olimpico`,
  `pais_a_buscar`
WHERE
  `medallero_olimpico`.`posicion` =
    IF(
      `pais_a_buscar`.`posicion` = 1,
      `pais_a_buscar`.`posicion`,
      `pais_a_buscar`.`posicion` - 1
    )
GROUP BY
  `pais_a_buscar`.`pais`,
  `medallero_olimpico`.`oro`,
  `medallero_olimpico`.`plata`,
  `medallero_olimpico`.`bronce`,
  `medallero_olimpico`.`total`;


pais
oro
plata
bronce
total


pais 1
1
2
3
6