CODEWITHSUNDEEP
mysqlsqlrandomgroup-by
Musikdoktor
Musikdoktor

Reputation: 144

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..

enter image description here

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..

enter image description here

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

enter image description here

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

Upvotes: 3

Views: 831

Answers (2)

forpas
forpas

Reputation: 164099

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

Upvotes: 2

Strawberry
Strawberry

Reputation: 33945

Hm, I really thought this would work, but it seems not to. I'm posting it anyway, in case it inspires others...

select * from test 
  order by field(nombre,
  (select group_concat(distinct concat('\'',nombre,'\'') order by rand()) from test)
  );

This seems to work...

select @i:= group_concat(distinct nombre order by rand()) from test;

select *,find_in_set(nombre,@i) n from test order by n,orden

Upvotes: 0

Related Questions