Mysql GROUP BY, how to detect when new "group"?
Hi,
When using "SELECT * FROM table GROUP BY city"
What is the best method to detect a new group/city?
I need to post a new html-table and list all related database records associated to each city.
Any suggestions?
When using "SELECT * FROM table GROUP BY city"
What is the best method to detect a new group/city?
I need to post a new html-table and list all related database records associated to each city.
Any suggestions?
ASKER
You're right.
Think i'm going to use ORDER BY and then check if new city is different from previous one, if so: post table start and ending.
Think that will work?
Think i'm going to use ORDER BY and then check if new city is different from previous one, if so: post table start and ending.
Think that will work?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"SELECT * FROM table GROUP BY city" is bad for 2 reasons (at least)
1. that * will produce changes that your downstream code may not be prepared for, it is a bug creator 'par excellence'
2. MySQL has this bizarre "feature" that allows this lazy syntax, and it "approximates" values for the fields (read: guesses)
In most flavours of SQL you MUST specify ALL the fields that will form a group, then ALL OTHER fields must be handled by an aggregate function like MAX() MIN() SUM() COUNT() etc.
The default settings of MySQL ignore this convention; and don't require the more stringent syntax. One risk is (if someone does alter the settings) and the system all of a sudden will require precise syntax and all previous lazy syntax grouping queries will fail. The other, perhaps more disturbing, risk is that the data in all the non-aggregated columns just isn't accurate and should be relied upon (unless you do enjoy "indeterminate" results).
Use these "lazy group bys" with caution IMHO
1. that * will produce changes that your downstream code may not be prepared for, it is a bug creator 'par excellence'
2. MySQL has this bizarre "feature" that allows this lazy syntax, and it "approximates" values for the fields (read: guesses)
In most flavours of SQL you MUST specify ALL the fields that will form a group, then ALL OTHER fields must be handled by an aggregate function like MAX() MIN() SUM() COUNT() etc.
The default settings of MySQL ignore this convention; and don't require the more stringent syntax. One risk is (if someone does alter the settings) and the system all of a sudden will require precise syntax and all previous lazy syntax grouping queries will fail. The other, perhaps more disturbing, risk is that the data in all the non-aggregated columns just isn't accurate and should be relied upon (unless you do enjoy "indeterminate" results).
MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.http://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html (emphasis added)
Use these "lazy group bys" with caution IMHO
You may be better off writing your App to select the Distinct Cities, and then loop through that list, firing off another query to select records that match that city.