'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'm trying to get the "from" and "to" fields from a table, but I only want the last 24h lines, and I need to return only the 20 most repeating lines of those 24h. But the above result returns the lines that are not the ones that repeat the most in the range of given dates.
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 you do this with GROUP BY. So it will group the records that contain the same value in the from and to fields and generate a field with the count of this repeating records.
By arranging for the num_clicks counter, in a decreasing order, you will first have the most repeatable records.
I consulted this post here:
Find records with a date field in the last 24 hours [duplicate]
and the one here:
to write this response.
To do this, it would look like this:
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;