Re: select count(*) and limit

El día Thu, 18 May 2006 14:18:07 +0200
Verena Ruff <lists@triosolutions.at> escribió:

> Oscar Rodriguez Fonseca schrieb:
> > But I do not get the point of the query.
> >
> the reason why I'm asking is that I have some rather complex queries and
> I'd like to present the result in a paged way. I need to get the 10
> records I'd like to present the user and I need to know how many records
> there are to calculate how many pages are needed and to create the
> neccessary links. The real query isn't as simple as the shown example,
> there are a few joins making it quite complex and slow. So I'm looking
> for a way to do this with one query and saving some time.

Another way of doing it can be using UNION ALL and using the first value as such. E.g:


TABLE IN DB:


number_times | user_name   | last_login
----------------------------------------
 1           | Paul        | 11-12-2005
 4           | Mary        | 08-11-2005
 5           | Charles     | 01-02-2005

[...]

SELECT count(*) AS number_times,NULL AS user_name,NULL as last_login \
FROM table UNION ALL SELECT * FROM table LIMIT 2;

This has the practical drawback that you need an integer column in your
table to get the query working as expected and the design drawback that
it is a little bit weird and somewhat non-standard way of querying.

I cannot think of another way of doing it. My guess is that limiting
the query results should imply two queries to get the number of
possible rows but these are my firsts steps with RDBMS so I may be wrong.

BTW, I had a similar problem and solved it storing the full result list
in a temporal variable on the server but "session-wise" (when the user
opens another unrelated page, the application frees the variable). This
is possible in my case because my app won't have more than 10 clients
at once and therefore it don't represent much memory overhead.

Regards.

--
Oscar