On this doc page for the query module, it says:
The pagination in the Query module works exactly like the Weblog and Comment Pagination with only one exception. In the Query module you cannot have a LIMIT clause on your query. Instead you have to use a limit=”“ parameter to specify how many results to display per page, and then ExpressionEngine will automatically modify your query to display the appropriate results.
After fiddling around with it myself, I find that the reverse is also true. Meaning, if you choose to use the limit=”“ parameter rather than using LIMIT within the actual query you MUST use pagination inside the query.
So, this works:
{exp:query limit="5" sql="SELECT title,entry_date FROM exp_weblog_titles ORDER BY entry_date DESC"}
<p>{title} - {entry_date format="%Y %m %d"}</p>
{paginate}
<p>Page {current_page} of {total_pages} pages {pagination_links}</p>
{/paginate}
{/exp:query}
And this works:
{exp:query sql="SELECT title,entry_date FROM exp_weblog_titles ORDER BY entry_date DESC LIMIT 5"}
<p>{title} - {entry_date format="%Y %m %d"}</p>
{/exp:query}
But this does not:
{exp:query limit="5" sql="SELECT title,entry_date FROM exp_weblog_titles ORDER BY entry_date DESC"}
<p>{title} - {entry_date format="%Y %m %d"}</p>
{/exp:query}
