Asad Habib <[EMAIL PROTECTED]> wrote on 03/30/2005 10:53:38 AM:
> Does MySQL 4.1 support the use of GROUP BY and ORDER BY used in
> conjunction with one another? I have tried to execute several queries
> with both these clauses but the result set I get is different from what
I
> expect. My queries read as follows:
>
> SELECT *, *, * FROM *
> WHERE *
> GROUP BY * ORDER BY *
>
> Also, does GROUP BY only work on fields that are strings (i.e.
> CHAR, VARCHAR, TEXT, etc.). Thanks in advance.
>
> - Asad
>
I hope you don't expect your query to actually work. The SELECT clause is
the only place where you can use the * wildcard to mean "all columns". If
you wanted to frame a sample query but leave out information, I have been
using an ellipsis (three dots together) to indicate the missing piece(s)
like this:
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
ORDER BY ...
However, I generally do not leave out EVERYTHING in a query, like the one
above. I usually only leave out the parts that aren't important to the
information I am trying to convey. If I want to indicate that there was
something specific the user needs to replace, I put that inside of "angle
brackets" like this
SELECT <a list of columns from your table>
, <a list of aggregate functions on columns from your table>
FROM <a table name>
GROUP BY <all of the columns in your SELECT clause that are not part of an
aggregate function>
These are just my conventions. Use them only if you like them. I am not
nor will I ever become the "style" police for this list. I just thought
you could use a little help in creating better sample queries.... :-)
You asked if you can use GROUP BY and ORDER BY in the same query.
Absolutely!! I do it frequently. You also asked if GROUP BY works on
different column types. Absolutely!! You can group on any type of field or
combination of datatypes supported by MySQL (with the exception of TEXT
and BLOB fields as they usually contain more data than is practical to use
to form aggregates. I would recommend that you do not use a BLOB or TEXT
field in a GROUP BY unless it is unavoidable. IF you MUST do it, then you
should manually specify what portion of the field to use)
May I suggest some reading?
http://dev.mysql.com/doc/mysql/en/select.html
http://dev.mysql.com/doc/mysql/en/group-by-functions-and-modifiers.html
http://dev.mysql.com/doc/mysql/en/blob.html
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
