Gotcha – SQL count() query with LIMIT clause
Bumped into an interesting feature today. Well, whether it’s a feature or a gotcha appears to be in the eye of the beholder. And, I’ll tell ya, for this particular beholder, it was a gotcha.
I’ve got some code that builds a SQL query from a variable set of parameters. Most parameters are involved with building a dynamic WHERE clause. Other parameters are for pagination, setting a LIMIT clause on the query that eventually gets built. Another parameter is whether to make the query a COUNT() query or whether to actually pull rows from the db.
In the case where I build a COUNT() query, I was carelessly allowing a zero-based LIMIT clause – something like LIMIT 0,10 – to be appended to the query. No sweat, I figured, since I really only need the single-row containing the count I am requesting.
However, it turns out that this LIMIT was actually be applied *before* the COUNT()-ing, not after. So, my COUNT() was completely wrong, often returning 0.
There’s an hour of my life – spent in furious frustration – that I’ll never get back.
Onward!
chris said,
Wrote on November 10, 2010 @ 9:52 am
easy enough just do a nested query…
your inner query is your limit, your outer query is the results from the inner plus your count…