MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS `test` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Nombre` varchar(50) COLLATE utf8_spanish2_ci DEFAULT NULL,
  `Orden` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci;
INSERT INTO `test` (`Id`, `Nombre`, `Orden`) VALUES
    (1, 'Mark', NULL),
    (2, 'David', 1),
    (3, 'John', 1),
    (4, 'David', 2),
    (5, 'John', 3),
    (6, 'John', 2),
    (7, 'William', NULL);

Records: 7  Duplicates: 0  Warnings: 0


select t.* 
from test t 
inner join (select Nombre, rand() rnd from test group by Nombre) r
on r.Nombre = t.Nombre
order by r.rnd, t.Orden


Id
Nombre
Orden


7
William
null

3
John
1

6
John
2

5
John
3

1
Mark
null

2
David
1

4
David
2


select t.* 
from test t 
inner join (select Nombre, rand() rnd from test group by Nombre) r
on r.Nombre = t.Nombre
order by r.rnd, t.Orden


Id
Nombre
Orden


7
William
null

1
Mark
null

2
David
1

4
David
2

3
John
1

6
John
2

5
John
3


select t.* 
from test t 
inner join (select Nombre, rand() rnd from test group by Nombre) r
on r.Nombre = t.Nombre
order by r.rnd, t.Orden


Id
Nombre
Orden


2
David
1

4
David
2

7
William
null

1
Mark
null

3
John
1

6
John
2

5
John
3