[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