I want to make a query that will return the album_id witch has played the most times in the last week.
This is what I came up so far, but it is not a valid query:
I'm not sure that you can use count like that. And if you could, COUNT(album_id) would always be the same because it refers to the amount of album_ids in the table. You sure you can't order by time?
$query = mysql_query('SELECT album_id, COUNT(album_id) as album_count
FROM counter
WHERE time > ' . (time() - 3600 * 24 * 7) . '
GROUP BY album_id
ORDER BY album_count DESC
LIMIT 1');
echo mysql_result($query, 0);