Group continuous repeats with Mysql

Asked

Viewed 94 times

1

I was wondering if there is any way to group repetitions using Mysql only.

If you make a SELECT * from tabela WHERE id_usuario = 1 ORDER BY id ASC will return:

id | id_usuario | Data       | Texto
0  | 1          | 2016-10-16 | Sua senha foi alterada
1  | 1          | 2016-10-17 | Sua senha foi alterada
2  | 1          | 2016-10-18 | Sua senha foi alterada
3  | 1          | 2016-10-19 | Sua chave de 2FA foi alterada
4  | 1          | 2016-10-19 | Sua chave de 2FA foi alterada
5  | 1          | 2016-10-20 | Seu acesso foi revogado
6  | 1          | 2016-10-20 | Sua senha foi alterada
7  | 1          | 2016-10-20 | Sua chave de 2FA foi alterada

I wish there was some function that would return exactly:

id | id_usuario | Data       | Repetição | Texto
2  | 1          | 2016-10-18 | 3         | Sua senha foi alterada
4  | 1          | 2016-10-19 | 2         | Sua chave de 2FA foi alterada
5  | 1          | 2016-10-20 | 1         | Seu acesso foi revogado
6  | 1          | 2016-10-20 | 1         | Sua senha foi alterada
7  | 1          | 2016-10-20 | 1         | Sua chave de 2FA foi alterada

Thus removing the repetitions and returning the number of repetitions in "Repetition", which could be generated as in a count(id) as Repetição, this would allow to inform that there was another notification of the same type and would have to expand to see duplicate notifications, making a new request.

mysql sql mariadb

1 answer

0

Use COUNT(), DISTINCT and GROUP BY.

Example:

SELECT id, id_usuario, Data, COUNT(DISTINCT Text) AS Repeticao, Text from table WHERE id_usuario = 1 GROUP BY id ASC

  • This will group everything and not just by repetition. Imagine this: 1 1 2 1, that each number is a line. In your case it would result in 3 and 1. So, 3 x "1" and 1 x "2"What I would wish would be: 2 x "1", 1 x "2", 1 x "1", then the count of 2 1 1. Because it has 2 times the "1" and then is interrupted by a "2" and then there is a new "1".

  • I traded ORDER BY for GROUP BY, so it will group by the ids you want.

Browser other questions tagged mysql sql mariadb

You are not signed in. Login or sign up in order to post.