Earlier quoted context omitted.
How else do you do it? Select the entire table and filter it at the application layer?
That depends upon the definition of "entire table". Most situations have already pre-filtered data to some reasonably small quantities -- e.g. category, genre, exchange date, etc. Yes, caching and working on that set is almost always the superior option.
I think this is where we're branching apart - I see pagination as part of that pre-filtering against a larger dataset.
Having a dataset your web server can crunch and cache is just one use case.