Query order by count()
Posted: Sat Jun 16, 2007 8:52 am
I have a table counter witch added and album_id and the current time when an album has played:
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:
Code: Select all
album_id time
----------------------
b730apuwiz 1181903777
c6rckff34s 1181903812
c6rckff34s 1181906378
b730b6twhf 1181912063
b730peqpzj 1181924276
c6rckff34s 1181927945
b730bx5nc2 1181987855
c6rckff34s 1181991699
----------------------This is what I came up so far, but it is not a valid query:
Code: Select all
$query = mysql_query('SELECT album_id
FROM counter
WHERE time + 3600 * 24 * 7 > ' . time() . '
ORDER BY COUNT(album_id) DESC LIMIT 1');
echo mysql_result($query, 0);