Mysql select Random with priorities

Question

I have the following scenario, I have a table and I need to select an order by RAND(). But I would like to put some conditions for example:

TABELA
ID | NOME | IDADE | GRUPO

Dice

1 | Hiago | 20 | 1
2 | Igor | 15| 1
3 | Ana| 18 | 2
4 | Fernanda| 19 | 4
5 | João | 20 | 5
5 | Tati | 16 | 2

I’d like to make a SELECT with the ORDER BY RAND() but in that SELECT I would put a LIMIT of 3 and I would like to ensure that in those 3 have column values GRUPO repeated only if there are no further entries in the column GRUPO.

What must happen

RETURN (CORRECT) WITH LIMIT 3:

2 | Igor | 15| 1
3 | Ana| 18 | 2
4 | Fernanda| 19 | 4

RETURN (INCORRECT) WITH LIMIT 3:

1 | Hiago | 20 | 1
2 | Igor | 15| 1
4 | Fernanda| 19 | 4

The return above was incorrect because he repeated the group 1 2 times and there were groups 2, 4 and 5 still to be shown.

RETURN (CORRECT) WITH LIMIT 5:

2 | Igor | 15| 1
3 | Ana| 18 | 2
4 | Fernanda| 19 | 4
5 | João | 20 | 5
1 | Hiago | 20 | 1

In this case it was correct because all the groups already appeared at least once so he repeated the code of group 1, but nothing would prevent him to repeat 2 since all appeared...

So let’s ask the question, how would I mount this SQL?

Answer 1

I think the easiest thing in this case would be to use a Procedure and make a loop drawing each line with the conditions. But I was able to think of a way to make the appointments without it.

Mysql commands to create table:

CREATE TABLE IF NOT EXISTS random_prioridade (
  id int NOT NULL,
  nome varchar(90) NOT NULL,
  idade int NOT NULL,
  grupo int NOT NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

INSERT INTO random_prioridade (id, nome, idade, grupo) VALUES
(1, 'Hiago', 20, 1),
(2, 'Igor', 15, 1),
(3, 'Ana', 18, 2),
(4, 'Fernanda', 19, 4),
(5, 'João', 20, 5),
(6, 'Tati', 16, 2);

On your Tati is with id 5, I switched to 6 to have no repeat id.

Query for LIMIT 3:

SELECT rp.id, rp.nome, rp.idade, rp.grupo
FROM random_prioridade rp, 
( SELECT a.id as id0, b.id as id1, c.id as id2
  FROM random_prioridade a, random_prioridade b, random_prioridade c
  WHERE a.id < b.id AND b.id < c.id
  ORDER BY ( cast(a.grupo=b.grupo as int) + cast(a.grupo=c.grupo as int) + 
             cast(b.grupo=c.grupo as int) ), rand()
  LIMIT 1
) selecionados 
WHERE rp.id IN (selecionados.id0, selecionados.id1, selecionados.id2)
ORDER BY rand()

Query for LIMIT 5:

SELECT rp.id, rp.nome, rp.idade, rp.grupo
FROM random_prioridade rp, 
( SELECT a.id as id0, b.id as id1, c.id as id2, d.id as id3, e.id as id4
  FROM random_prioridade a, random_prioridade b, random_prioridade c, random_prioridade d, random_prioridade e
  WHERE a.id < b.id AND b.id < c.id AND c.id < d.id AND d.id < e.id
  ORDER BY ( cast(a.grupo=b.grupo as int) + cast(a.grupo=c.grupo as int) + cast(a.grupo=d.grupo as int) + 
             cast(a.grupo=e.grupo as int) + cast(b.grupo=c.grupo as int) + cast(b.grupo=d.grupo as int) + 
             cast(b.grupo=e.grupo as int) + cast(c.grupo=d.grupo as int) + cast(c.grupo=e.grupo as int) +
             cast(d.grupo=e.grupo as int) ),  rand()
  LIMIT 1
) selecionados 
WHERE rp.id IN (selecionados.id0, selecionados.id1, selecionados.id2, selecionados.id3, selecionados.id4)
ORDER BY rand()

To do with another number in LIMIT just use the same logic. But I think that above 5 is already impossible to write the query.