Currently, you cannot use a variable in the LIMIT statement (there is already a feature request for this functionality). You can, however, use the PREPARE syntax for dynamic SQL.
however, are you sure that you want to use a function for this type of stuff? It would be more efficient to simply execute a standard query:
SELECT MAX(enddate) as maxdate
FROM orderitems
WHERE orderNumber = @orderNum;
If you needed to use this in another query, just use a subquery:
SELECT * FROM orderitems
WHERE orderNumber = @orderNum
AND enddate =
(
SELECT MAX(enddate) as maxdate
FROM orderitems
WHERE orderNumber = @orderNum
);
or if you want to get all order items for ALL orders that were ordered on on each order's last date, use a derived table:
SELECT * FROM orderitems oi
INNER JOIN (
SELECT orderNumber, MAX(enddate) as maxdate
FROM orderitems
GROUP BY orderNumber
) dt
ON oi.orderNumber = dt.orderNumber
AND oi.enddate = dt.enddate;
This last query assumes, of course, that there is a unique key on (orderNumber, enddate). If not, then some things might have to be changed a bit.
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com