MySQL select random with priorities

Question:

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

TABELA
ID | NOME | IDADE | GRUPO

Data

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 would like to make a SELECT with ORDER BY RAND() but in this SELECT I would put a LIMIT of 3 and I would like to ensure that these 3 have repeated GRUPO column values ​​only if there are no more distinct records in the GRUPO column.

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 above return was incorrect because he repeated group 1 2 times as there were groups 2, 4 and 5 yet to be displayed.

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 have already appeared at least once so he repeated the code for group 1, but nothing would prevent him from repeating the 2 since they all appeared…

So let's ask the question, how would I build this SQL?

Answer:

I think the easiest thing in this case would be to use a procedure and make a loop sorting each line with the conditions. But I managed to think of a way to do the queries without it.

MySQL commands to create the 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 it has an id of 5, I changed it to 6 to not have any repeated 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 it is already impossible to write the query.

Scroll to Top