Hi Johan,
Johan De Meersman wrote:
as a totally off-topc question, wouldn't something along the lines of
LIMIT COUNT(*)/2, 1 do that trick?
On 4/15/10, Rhino <rhi...@sympatico.ca> wrote:
...snip...
For example, suppose you had to determine the median grade for a test.
...
That would require running the query twice as LIMIT only accepts numeric
literals. For a large dataset, that would destroy the efficiency of the
stored procedure. It's more efficient with MySQL to capture the value in
a temporary table, count those temporary results, then create a LIMIT
query using the prepared statement syntax (dynamic SQL) against the data
in the temp table.
http://dev.mysql.com/doc/refman/5.1/en/select.html
####
The LIMIT clause can be used to constrain the number of rows returned by
the SELECT statement. LIMIT takes one or two numeric arguments, which
must both be nonnegative integer constants (except when using prepared
statements).
####
The above process could very easily be encapsulated by a stored
PROCEDURE (but not by a stored FUNCTION) so that you would not need to
implement it in your client code. Unfortunately the stored functions are
not allowed to use prepared statements, yet.
http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html
####
SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be
used in stored procedures, but not stored functions or triggers.
####
Hope that helps!
--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
