Page 1 of 1

Query order by count()

Posted: Sat Jun 16, 2007 8:52 am
by WaldoMonster
I have a table counter witch added and album_id and the current time when an album has played:

Code: Select all

album_id    time
----------------------
b730apuwiz  1181903777
c6rckff34s  1181903812
c6rckff34s  1181906378
b730b6twhf  1181912063
b730peqpzj  1181924276
c6rckff34s  1181927945
b730bx5nc2  1181987855
c6rckff34s  1181991699
----------------------
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

$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);

Posted: Sat Jun 16, 2007 9:10 am
by feyd
Lose the math in the query. Do the math in PHP (against time() or just use strtotime())

Posted: Sat Jun 16, 2007 10:14 am
by WaldoMonster
The math is not the problem.
But is it better to do he math in PHP than in MySQL?

The error message is related to the ORDER BY COUNT(album_id) section,
Is there an alternative for?

Posted: Sat Jun 16, 2007 10:58 am
by superdezign
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?

Posted: Sat Jun 16, 2007 11:58 am
by WaldoMonster
I found the solution here: http://www.vbulletin.com/forum/archive/ ... -3748.html

Code: Select all

$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);

Posted: Sat Jun 16, 2007 12:48 pm
by superdezign
Awesome. :D

I was having the same problem as you this morning (except it was just to select, not order by).