Mysql: query con selezione casuale e ordine nei raggruppamenti

Mysql mette a disposizione la funzione rand() la quele genera un valore in virgola mobile con risultati che possono variare da 0 a 1.

La funzione può essere utilizzata anche per fare estrarre a una SELECT dei record in ordine casuale.

Nel seguente esempio abbiamo una tabella con la seguente struttura:

CREATE TABLE `province_comuni` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`provincia` VARCHAR( 2 ) NOT NULL ,
`comune` VARCHAR( 50 ) NOT NULL
)

da cui vogliamo estrarre 10 comuni casualmente e lo faremo con la seguente query:

SELECT comune
FROM province_comuni
  ORDER BY rand( )
  LIMIT 10

Volendo estrarre casualmente 10 comuni di 10 province diverse anch’esse estratte accaso potremo pensare erroneamente che la seguente query possa funzionare:

SELECT comune, provincia
  FROM province_comuni
  GROUP BY provincia
  ORDER BY rand( )
  LIMIT 10

questa raggruppa per provincia le quali vengono estratte a caso ma i comuni di ognuna d essa manterranno un ordinamento non casuale, questo perché nella SELECT viene eseguito prima il GROUP BY e soltanto dopo l’ORDER BY.
Il problema si risolve facendo prima una SELECT ordinata a due livelli, per provincia e per caso (ORDER BY provincia, rand() )  sulla quale poi eseguiremo un’ulteriore SELECT raggruppando il tutto per provincia e ordinando in modo random:

SELECT *
  FROM (
    SELECT *
      FROM province_comuni
      ORDER BY provincia, rand( )
    ) AS tmp_rand
  GROUP BY provincia
  ORDER BY rand( )
  LIMIT 10