MAX(COUNT()) Possible wiithout sub-queries ?

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
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

MAX(COUNT()) Possible wiithout sub-queries ?

Post by anjanesh »

Any way to solve for MAX(COUNT(`ColumnName`)) without using sub-queries ? Since its MySQL 4.0 and not MySQL 4.1
I cannot use ORDER BY DESC because the result will be many rows where each row gives the max of a particular group.

Code: Select all

SELECT
DATE_FORMAT(`Entry`,'%M %Y') AS `Month`,
MAX(COUNT(DISTINCT `ID`))
FROM `Table1`
GROUP BY `Month`
ORDER BY LEFT(`Entry`,7) ASC
There seems to be a possible method : http://dev.mysql.com/doc/mysql/en/examp ... p-row.html
But I can't get it figured out.
Any way out ?
Thanks
Post Reply