Find lines of the last 24 hours that repeat the most (Mysql)

Question

'SELECT `from`, `to`, count(*) AS num_clicks
FROM my_rank
WHERE my_rank_data
BETWEEN "'.$yesterday.'" AND "'.$today.'"
ORDER BY num_clicks DESC LIMIT 0,20';

Above I am trying to take the fields "from" and "to" of a table, but I only want the lines of the last 24 hours, and I need to return only the 20 lines that most repeat of these 24 hours. However the above result returns me lines that are not the ones that are most repeated in the given date range.

Answer 1

You can do it yes, I’d do it that way:

SELECT `from`, `to`, COUNT(*) AS num_clicks
FROM my_rank
WHERE my_rank_data >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY `from`, `to`
ORDER BY num_clicks DESC
LIMIT 20;

Answer 2

You can do something like this:

SELECT `from`, `to`, COUNT(*) AS num_clicks
FROM my_rank
WHERE my_rank_data >= NOW() - INTERVAL 1 DAY
GROUP BY `from`, `to`
ORDER BY num_clicks DESC

To use an aggregate function such as COUNT(), you need to indicate how you want to group the records (in this case, to do this count), and this you do with GROUP BY. So it will group the records that contain the same value in the fields from and to and generate a field by counting this repetition of records.

When organizing by the counter in a few clicks, descending, you will first have the most repeating records.

I consulted this post here:

Find Records with a date field in the last 24 hours [Uplicate]

and this one:

Find Most Frequent value in SQL column

to write this reply.