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.
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.
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.
The writer of http://www.pierrebuckley.com has written a superior article. I got your point and there is nothing to argue about. It is like the following universal truth that you can not disagree with: The best humor in the work place is about the Management. I will be back.
Thank you for the compliment. Much appreciated.
I am very impressed with the article I have just read. I wish the author of http://www.pierrebuckley.com can continue to provide so much practical information and unforgettable experience to http://www.pierrebuckley.com readers. There is not much to say except the following universal truth: The greatest threat to any computer is a determined three year old with a screwdriver. I will be back.
You have to express more your opinion to attract more readers, because just a video or plain text without any personal approach is not that valuable. But it is just form my point of view
I apologise, but you could not paint little bit more in detail.
Perhaps, I shall agree with your opinion
It seems excellent phrase to me is
The matchless message, is very interesting to me
Yes it is all a fantasy
I apologise, but you could not paint little bit more in detail.
Thanks for the recommendation. I will use it in my subsequent writings.
It’s a bit strange to get count(*) with a limit offset,count.
Unless you have fewer then count records remaining the result will always be count.