[thelist] MySQL SELECT statement and functions

rudy r937 at interlog.com
Wed Jun 27 22:27:02 CDT 2001


> I'm trying to execute an sql select statement in MySQL,
> but wish to use a function in the ORDER BY clause.
> This gives an error.

hi jon

patient:  "doc, it hurts when i do this"
doctor: "don't do it"

;o)

according to
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#SE
LECT --

   "Note that if you are using MySQL Version 3.22 (or earlier)
    or if you are trying to follow ANSI SQL, you can't use expressions
    in GROUP BY or ORDER BY clauses. You can work around this
    limitation by using an alias for the expression:

    mysql> SELECT id,FLOOR(value/100) AS val
                  FROM tbl_name
                  GROUP BY id,val ORDER BY val;

this seems to suggest that you are not using a later version

which is okay, because it's often better to use ansi sql if you can

so just add the funtion to your SELECT list --

    SELECT *, Floor(orderid) as foo
       FROM $table_name
    WHERE orderid NOT LIKE '%x%'
    ORDER BY foo ASC;

yes, this generates an additional column, but you're getting them all
anyway, so it hardly makes a difference...


rudy





More information about the thelist mailing list