MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

SELECT VERSION();


VERSION()


8.0.11


DROP TABLE IF EXISTS `alumnos`;
CREATE TABLE IF NOT EXISTS `alumnos` (
  `nombre_a` VARCHAR(25) NOT NULL,
  `id_escuelas` BIGINT UNSIGNED NOT NULL
);
INSERT INTO `alumnos`
  (`nombre_a`, `id_escuelas`)
VALUES
  ('Juan', 1),
  ('Roman', 2),
  ('Raul', 3),
  ('Omar', 1),
  ('Jose', 2);
SELECT
  `x`.`nombre_a`,
  `x`.`id_escuelas`
FROM (
  SELECT
    `alumnos`.`id_escuelas`,
    `alumnos`.`nombre_a`,
    ROW_NUMBER() OVER(PARTITION BY `alumnos`.`id_escuelas`) AS `rank`
  FROM
    `alumnos`
) `x`
ORDER BY `x`.`rank`, `x`.`id_escuelas`;


nombre_a
id_escuelas


Juan
1

Roman
2

Raul
3

Omar
1

Jose
2