Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email firstname.lastname@example.org
From: Shawn Green (shawn.l.greenoracle.com)
Date: Sun Apr 18 2010 - 08:47:42 CDT
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 <rhino1sympatico.ca> wrote:
>> 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.
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
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.
SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be
used in stored procedures, but not stored functions or triggers.
Hope that helps!
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql