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 |