MySQL select random with priorities

Question

MySQL select random with priorities

Navigation

#1 by (1 votes)

3

I have the following scenario, I have a table and I need to make a select with an order by RAND (). But I'd 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 values of column GRUPO repeated only if you do not have more records in the GRUPO column.

What should happen

RETURN (CORRECT) COM 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 it repeated group 1 2 times and there were groups 2, 4 and 5 still to be displayed.

RETURN (CORRECT) COM 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 from repeating the 2 since they all appeared ...

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

mysql sql random sql-select select

asked by anonymous 21.11.2015 / 02:02

1 answer

Select without the id field (RoR) PHP embedded in HTML in Eclipse

score 1 · Answer 1

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

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);

In your Tati is with id 5, I changed by 6 to have no id repeated.

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 over 5 is already unfeasible to write the query.