Guys - I think this fusing of GROUP BY and ORDER BY is a bad thing - even if it works. GROUP should group and if you want an order - add an ORDER clause explicitly. Yes, the GROUP BY does a sort and you are tempted to leverege it but it could lead to problems.
It may not be visible in single-threaded MySQL, but in Oracle for example, which allows you to run queries in parllel - it becomes clear. In parallel query - each query server does part of the work and one of them (the "uber" query server) glues the partial results together. For example - Let's say you want to count students by class code. You run a 2-way query - SELECT CLASS_CODE, COUNT(*) FROM TABLE GROUP BY CLASS_CODE. You can get this result: CLASS1 111 CLASS2 222 CLASS3 333 CLASS4 444 (the first 2 rows came from query server 1 and the last 2 came from query server 2). However, you can also get this result: CLASS3 333 CLASS4 444 CLASS1 111 CLASS2 222 (the first 2 rows came from query server 2 and the last 2 came from query server 1). Both satisfy the GROUP BY requirement. Within each sub-result it's sorted but the glued total is not. Oracle requires an explicit ORDER BY to make sure the entire result is sorted, which makes sense to me - gives you the option to save on cycles if you just need the grouping and not the ordering. MySQL, which has big plans for the future - will no doubt introduce parallel query one day - it's a bread and butter requirement for data warehousing, and then the issue will come up. A little extra effort up front - and you won't get killed when the database engine tightens its standards. IMHO. In a message dated 5/23/2004 8:24:15 PM Eastern Daylight Time, [EMAIL PROTECTED] writes: > > It's right there in the manual <http://dev.mysql.com/doc/mysql/en/SELECT.html>: > > | If you use GROUP BY, output rows are sorted according to the GROUP BY > | columns as if you had an ORDER BY for the same columns. MySQL has extended > | the GROUP BY clause as of version 3.23.34 so that you can also specify ASC > | and DESC after columns named in the clause: > | > | SELECT a, COUNT(b) FROM test_table GROUP BY a DESC > > Michael > > Daniel Kasak wrote: > > > Hi all. > > > > I've noticed that if I do a 'group by xxx' I get the results sorted by > > xxx ( when I'm only grouping on 1 field ). > > It seems to happen that way every single time. > > Is this faster than doing both a 'group by' and 'sort by', and is it > > something I can rely on? > > Just curious... > > > > Dan > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
