SQL Query with Group , Order by and Random at the same time

I was unable to find anything similar to this problem.

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);

As we see John and David has more than one row and there’s a Order column so we can order it simply using ORDER BY Name ASC, Order ASC but this is not exactly what i need..

Here is the problem: i would like to know if its possible to order by name and make some kind of group for each name, then by order by its Order, and then apply a final ORDER BY RAND() so you still viewing all the rows from david, mark, john and William, but in random order..

So every time you run the query, the order is completely random but still with some order..

Here is a Fiddle http://sqlfiddle.com/#!9/038bd7/7

Answer

This query:

select Nombre, rand() rnd 
from test 
group by Nombre

returns a random number for each unique name in the table.
Join it to the table and sort first by that random number and then by Orden:

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

See the demo.
Results:

> Id | Nombre  | Orden
> -: | :------ | ----:
>  7 | William |  null
>  1 | Mark    |  null
>  2 | David   |     1
>  4 | David   |     2
>  3 | John    |     1
>  6 | John    |     2
>  5 | John    |     3

> Id | Nombre  | Orden
> -: | :------ | ----:
>  2 | David   |     1
>  4 | David   |     2
>  3 | John    |     1
>  6 | John    |     2
>  5 | John    |     3
>  1 | Mark    |  null
>  7 | William |  null

> Id | Nombre  | Orden
> -: | :------ | ----:
>  2 | David   |     1
>  4 | David   |     2
>  1 | Mark    |  null
>  7 | William |  null
>  3 | John    |     1
>  6 | John    |     2
>  5 | John    |     3