Subqueries

[masterlodi]

My host provider is running MySQL 4.0.24.

I have this query

SELECT `hbsclasses`.`classID`,
`hbsclasses`.`className`,
COUNT(`hbscompanies`.`classID`) AS Totals
FROM `hbscompanies`,
`hbsclasses`
WHERE `hbscompanies`.`classID` = `hbsclasses`.`classID` GROUP BY `hbscompanies`.`classID`
ORDER BY Totals DESC LIMIT 0,25";

I need the results of this query to be sorted alphabetically on `hbsclasses`.`className`.

 

[rac2]

Anything in the SELECT clause or the ORDER BY clause must be in the GROUP BY clause. Think of the GROUP BY as defining a new table; the columns used to group things are the columns available to display in the SELECT list or to sort by in the ORDER BY list.

So you may have a little problem if a company can have many classes; but not if it is the class which has many companies.

In the latter case, change the query to GROUP BY `hbsclasses`.`classID`.
And since there will be one className for each classID, you can group on both columns. So-

SELECT `hbsclasses`.`classID`, 
       `hbsclasses`.`className`, 
        COUNT(`hbscompanies`.`classID`) AS Totals 
FROM `hbscompanies`, 
     `hbsclasses`
WHERE `hbscompanies`.`classID` = `hbsclasses`.`classID`
GROUP BY ``hbsclasses`.`classID`, `hbsclasses`.`className` 
ORDER BY Thbsclasses`.`className DESC LIMIT 0,25";

Since there will be one Totals for each class you must decide to ORDER BY one or the other, you cant have it both ways.

I have never seen those backward apostrophes in SQL before. What do they mean?

Perhaps, I answered the wrong question. Let me know.

 

[r937]

the backwards apostrophes are normally called backticks

they are what mysql uses to delimit names which contain special characters

phpmyadmin uses them by default on every name

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[masterlodi]

Thanks rac2,

I'm using the order by and limit to find the top 25 classes. I don't know if this can be done any better (but it works).

r937 is correct, the back ticks are used by phpMyAdmin.