Query order by count()

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Query order by count()

Post 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);
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Lose the math in the query. Do the math in PHP (against time() or just use strtotime())
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Post 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?
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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?
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Post 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);
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Awesome. :D

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