MySQL, SELECT * FROM t WHERE c = {Most Repeated Record}

Question

To a large extent, as the name says, this was the easiest way to explain it. To develop ...

First I need to find the value of column c, which was duplicated in most cases (mostDuplicated), and then SELECT * FROM t WHERE c=mostDuplicated

Continue on ...

Here is my details:

 SELECT * FROM t a, b, c - - - 1, 1, 1 2, 2, 1 3, 3, 1 4, 4, 2 5, 5, 3 

So, completely ignore the values ​​in columns a and b, just focus on column c. I need to find the most duplicated value in column c (which is 1), and then SELECT only those WHERE c = 1 entries. I want to do this in one query, if possible.

Answer 1

Make a "group by" query to count the number of unique values ​​of c , sort it in descending order, and select only the top row. Then use the output as a subquery to select the rows with this particular c value:

 SELECT * FROM t WHERE c = (SELECT c FROM t GROUP BY c ORDER BY COUNT(*) DESC LIMIT 1) 

Answer 2

SELECT c FROM t GROUP BY c ORDER BY count(*) DESC LIMIT 1

Answer 3

Ok, so it will be:

 SELECT * FROM t WHERE c = (SELECT c FROM (SELECT c, count(c) as co FROM t ORDER BY co DESC LIMIT 1)) 

Hope for this help

Answer 4

Here you go, this is a bit confusing:

 SELECT * FROM t WHERE ( c IN ( SELECT c FROM ( SELECT c, COUNT(c) as freq FROM t GROUP BY c ORDER BY freq DESC, c ASC LIMIT 1 ) AS t2 ) ) 

Basically, this happens: 1. determine how often each C value is repeated 2. select the MAXimum value of repetitions 3. use this value to determine which C value to use when selecting * from the entire table.