Sort by the number of repetitions of a column?

Navigation
4

In a query in MySql I need to select the results and sort the results based on how much they are repeated using the id_livro column, for example:

My comments table: 

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

In this way I would like to organize a search 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 book 3, 2 and 1

How would this query look? Ps: Results need to be grouped by book id as well.

    
asked by anonymous 02.02.2017 / 18:35

2 answers

3

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

SELECT id_livro,
       COUNT(id_livro) AS quantidade
  FROM comentarios
 GROUP BY id_livro
 ORDER BY quantidade DESC
    
02.02.2017 / 18:40
3

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

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;

Output: 

+------------+-----------------------+
| nome       | numero_de_comentarios |
+------------+-----------------------+
| livro b    |                     3 |
| livro a    |                     2 |
| livro c    |                     1 |
| livro d    |                     0 |
+------------+-----------------------+
    
02.02.2017 / 18:54
Finding XPath in Selenium Regular expression for password