查找最活跃的玩家（表中符合条件的条目数最多）

Question

My goal is to show the top five players who have the highest number of new records in the past month.

Is there a method that uses only a single query or do I need to use PHP to complete my goal? Here is a simplified version of how I've already accomplished this (I guess this is more of a performance question. Also please ignore the CodeIgniter; it's superfluous):

$res = [];

$q = $this->db1->query('SELECT authid, name FROM table WHERE date BETWEEN NOW() - INTERVAL 30 DAY AND NOW() ORDER BY date DESC');

foreach ($q->result_array() as $row) {
    if (isset($res[$row['authid']])) {
        $res[$row['authid']]['records'] += 1;
    } else {
        $res[$row['authid']] = [
            'records'   => 1,
            'name'      => $row['name']
        ];
    }
}

arsort($res);

return array_slice($res, 0, 5);

Answer 1

You want group by and order by and limit:

SELECT authid, COUNT(*) as cnt
FROM table
WHERE date BETWEEN NOW() - INTERVAL 30 DAY AND NOW() 
GROUP BY authid 
ORDER BY cnt DESC
LIMIT 5;