Sort by the number of repetitions in a column?

Asked

Viewed 558 times

4

In a query in Mysql I need to select the results and sort the results based on the amount they repeat using the column id_livro, for example:

My table comments:

+----------------+-------------+
| id_livro       | comentario  |
+----------------+-------------+
|     1          | Com1        |
+----------------+-------------+
|     1          | Com2        |
+----------------+-------------+
|     2          | Com3        |
+----------------+-------------+
|     3          | Com4        |
+----------------+-------------+
|     3          | Com5        |
+----------------+-------------+
|     3          | Com6        |
+----------------+-------------+

In this way I would like to organize a research that returns the most commented books in descending order from the most commented ones to the less commented ones that in the case would be the book 3, 2 and the 1

What would that consult look like? Ps: Results need to be grouped by book id as well.

mysql sql select

2 answers

4


Just put a COUNT of the records and a ORDER BY in the end:

SELECT id_livro,
       COUNT(id_livro) AS quantidade
  FROM comentarios
 GROUP BY id_livro
 ORDER BY quantidade DESC
  • 1

    Thanks Sorack exactly what I needed, I will open another question more or less the same way, I hope it can help me there also you always save me when the subject is mysql

3

Assuming you have two tables, one for books and one for comments, you could do so:

Table livro:

CREATE TABLE livro (
    id INT,
    nome VARCHAR(25),
    PRIMARY KEY(id)
)

INSERT INTO livro VALUES(1, "livro a");
INSERT INTO livro VALUES(2, "livro b");
INSERT INTO livro VALUES(3, "livro c");
INSERT INTO livro VALUES(4, "livro d");

Table comentario:

CREATE TABLE comentario (
    id INT,
    livro_id INT,
    comentario VARCHAR(25),
    PRIMARY KEY(id),
    FOREIGN KEY(livro_id) REFERENCES livro(id)
)

INSERT INTO comentario VALUES(1, 1, "comentario 11");
INSERT INTO comentario VALUES(2, 1, "comentario 12");
INSERT INTO comentario VALUES(3, 2, "comentario 21");
INSERT INTO comentario VALUES(4, 2, "comentario 22");
INSERT INTO comentario VALUES(5, 2, "comentario 23");
INSERT INTO comentario VALUES(6, 3, "comentario 31");

Select:

SELECT l.nome,
       count(c.id) AS numero_de_comentarios FROM livro l
LEFT JOIN comentario c ON l.id = c.livro_id
GROUP BY c.livro_id
ORDER BY numero_de_comentarios DESC;

Exit:

+------------+-----------------------+
| nome       | numero_de_comentarios |
+------------+-----------------------+
| livro b    |                     3 |
| livro a    |                     2 |
| livro c    |                     1 |
| livro d    |                     0 |
+------------+-----------------------+

Browser other questions tagged mysql sql select

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