Alternative to MySQL’s LIMIT Command
I recently ran into the situation where I needed to create a query in MySQL that counted up to n rows from a result set. I scoffed at the challenge and proceeded to go for the one tool I thought could do the job – MySQL's LIMIT command. Unfortunately, the result I got was not what I had expected.
The Problem
If I had a table like below:
| id | First Name | Last Name | Salary |
| 1 | John | Brown | 150,000 |
| 2 | Pierre | Buckley | 200,000 |
| 3 | Hillary | White | 170,000 |
| 4 | Ferdinand | McBean | 100,000 |
I would expect that the following query would return a count of 1.
select count(*) from Employees limit 0,1
Instead, I got a value of 4 for the count which would be representative of all the rows in the table. And so it seems, that the LIMIT command only applies to the single record that is returned when the count aggregate function is used. This was useless to me.
The Solution
I went searching for a solution on Google and came across this post on stackoverflow where someone had asked a similar question. In one of the solutions that were given, there was mention of how it is implemented on Oracle. That led me to test it out in MySQL and it worked. Here's what I did.
select count(*) from (select * from Employees limit 1,0) as emp_table
This worked like a charm, albeit was a bit messy. I could live with it though as this was only being done for an assignment I had to complete for school. As such, the possible resulting performance penalty that may be associated with this method was not an issue. Please note that you must define an alias for your sub query.
I hope this information is useful to someone out there. If it is, please don't hesitate to comment. Chao.
