"Michael A. Smith" wrote:
>
> I have two tables: a category table and a main table. Each record in the
> main table belongs to a category (through reference to a PRIMARY KEY in the
> category table).
>
> I want to do a SELECT and get back a list of categories ordered by the
> number of records in the main table. In other words, I want to know the
> order of popularity of the categories.
>
> CATEGORY
> --------
> ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
> name CHAR(20) NOT NULL
>
> MAIN
> ----
> ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
> categoryID TINYINT UNSIGNED NOT NULL,
> a_field CHAR(40) NOT NULL
>
> SELECT CATEGORY.name FROM CATEGORY, MAIN WHERE MAIN.categoryID=CATEGORY.ID
> ORDER BY ?????????
>
> ****************
> Michael A. Smith | [EMAIL PROTECTED]
Hi Michael
Try this:
SELECT CATEGORY.name, count(*) AS n FROM CATEGORY, MAIN WHERE
MAIN.categoryID=CATEGORY.ID GROUP BY CATEGORY.name ORDER BY n
Tschau
Christian
-----------------------------------------------------------
Send a mail to [EMAIL PROTECTED] with
unsubscribe mysql [EMAIL PROTECTED]
in the body of the message to unsubscribe from this list.
