0
311
ViewsSQL 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
2 answers
Answer question0
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
0
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