MySQL Forums
Forum List  »  Newbie

Re: Using a variable with LIMIT in SELECT statement
Posted by: Felix Geerinckx
Date: October 31, 2005 01:23PM

CMS wrote:

> SELECT * FROM `Candidate` LIMIT N;

This result of this query is unpredictable, unless you include an ORDER BY clause to define the order of the rows.


> This fails at 'LIMIT N' because, apparently, LIMIT has to be followed by a literal constant.
>
> Is there a way to do this ...

If we assume you are e.g. ordering by name, you can use:

SELECT
*
FROM Candidate C1
WHERE
(SELECT COUNT(*) FROM Candidate WHERE name < C1.name ) < N
ORDER BY name;

at the expense of a correlated subquery.

> ... or do I need to construct the SQL in my client and give up on using a stored procedure whenever I need a variable number of results?

Performancewise, this would be your best option i think.

--
felix
Please use BBCode to format your messages in this forum.

Options: ReplyQuote


Subject
Written By
Posted
Re: Using a variable with LIMIT in SELECT statement
October 31, 2005 01:23PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.