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.]
Sheesh, it's hard to please everyone, but let's give it a try.:-) To remove the automatic sort that is produced by a Group By, do this:
SELECT CLASS_CODE, COUNT(*) FROM TABLE GROUP BY CLASS_CODE order by 'x'
You simply add an Order By clause and sort it by a constant. Since the constant never changes, the rows will appear in the same order as they were found.
Mike
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]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
