Hi,

sometimes I have queries with a LIMIT statement. Now I'd like to present
the user the returned records and inform him how many records there are
if there was no LIMIT statement. Is it possible to get all neccessary
information with one query?
This works:
SELECT * FROM table LIMIT 20
SELECT count(*) FROM table
But is it possible to have one query returning both, the records and the
count?

regards,
Verena

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On 5/18/06 6:51 AM, "Verena Ruff" <lists@triosolutions.at> wrote:

> Hi,
>
> sometimes I have queries with a LIMIT statement. Now I'd like to present
> the user the returned records and inform him how many records there are
> if there was no LIMIT statement. Is it possible to get all neccessary
> information with one query?
> This works:
> SELECT * FROM table LIMIT 20
> SELECT count(*) FROM table
> But is it possible to have one query returning both, the records and the
> count?

Verena

I think the answer is "no", not when using "LIMIT". However, For the count
part, a trick to speed things up is to use the output from EXPLAIN to
approximate the number of rows. If the table has been vacuumed on a regular
basis, the results are often pretty close to those returned by count(*). An
alternative to using the LIMIT clause is to use a cursor, but the ability to
do so depends on the environment in which you are working. In a web
environment, cursors are not useful given the stateless nature of the web
interface. Cursors are explained in the Docs.

Sean


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

lists@triosolutions.at (Verena Ruff) wrote in news:446C519E.6050507
@triosolutions.at:

> Hi,
>
> sometimes I have queries with a LIMIT statement. Now I'd like to present
> the user the returned records and inform him how many records there are
> if there was no LIMIT statement. Is it possible to get all neccessary
> information with one query?
> This works:
> SELECT * FROM table LIMIT 20
> SELECT count(*) FROM table
> But is it possible to have one query returning both, the records and the
> count?

Is this usable?

SELECT * FROM (SELECT count(*) FROM table) as tb, table limit 20;



--
Rolf

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

> sometimes I have queries with a LIMIT statement. Now I'd like to present
> the user the returned records and inform him how many records there are
> if there was no LIMIT statement. Is it possible to get all neccessary
> information with one query?
> This works:
> SELECT * FROM table LIMIT 20
> SELECT count(*) FROM table
> But is it possible to have one query returning both, the records and the
> count?

A surely _INEFFICIENT_ way of doing it:


SELECT t.*,c.count FROM table AS t FULL OUTER JOIN (SELECT count(*) FROM
table) AS c ON true LIMIT 20;

This will add a 'count' column at the end of each row (with the same value for all).

But I do not get the point of the query.

Regards,

--
Oscar

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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.

Regards,
Verena

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Sean Davis schrieb:
>
> On 5/18/06 6:51 AM, "Verena Ruff" <lists@triosolutions.at> wrote:
>
>
>> Hi,
>>
>> sometimes I have queries with a LIMIT statement. Now I'd like to present
>> the user the returned records and inform him how many records there are
>> if there was no LIMIT statement. Is it possible to get all neccessary
>> information with one query?
>> This works:
>> SELECT * FROM table LIMIT 20
>> SELECT count(*) FROM table
>> But is it possible to have one query returning both, the records and the
>> count?
>>
>
> Verena
>
> I think the answer is "no", not when using "LIMIT". However, For the count
> part, a trick to speed things up is to use the output from EXPLAIN to
> approximate the number of rows. If the table has been vacuumed on a regular
> basis, the results are often pretty close to those returned by count(*).
My chosen example was to simple, sorry for that. The real query isn't
just from one table, it contains a few joins, so I guess this trick
won't work here.

> An
> alternative to using the LIMIT clause is to use a cursor, but the ability to
> do so depends on the environment in which you are working. In a web
> environment, cursors are not useful given the stateless nature of the web
> interface. Cursors are explained in the Docs.
>
The queries are for a webpage, so coursers won't be usefull.

Regards,
Verena

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On 5/18/06 8:22 AM, "Verena Ruff" <lists@triosolutions.at> wrote:

> Sean Davis schrieb:
>>
>> On 5/18/06 6:51 AM, "Verena Ruff" <lists@triosolutions.at> wrote:
>>
>>
>>> Hi,
>>>
>>> sometimes I have queries with a LIMIT statement. Now I'd like to present
>>> the user the returned records and inform him how many records there are
>>> if there was no LIMIT statement. Is it possible to get all neccessary
>>> information with one query?
>>> This works:
>>> SELECT * FROM table LIMIT 20
>>> SELECT count(*) FROM table
>>> But is it possible to have one query returning both, the records and the
>>> count?
>>>
>>
>> Verena
>>
>> I think the answer is "no", not when using "LIMIT". However, For the count
>> part, a trick to speed things up is to use the output from EXPLAIN to
>> approximate the number of rows. If the table has been vacuumed on a regular
>> basis, the results are often pretty close to those returned by count(*).
> My chosen example was to simple, sorry for that. The real query isn't
> just from one table, it contains a few joins, so I guess this trick
> won't work here.

It should still work just fine, again with the caveat that it is an
approximation and depends on the statistics available. Try comparing the
output a few times for your count(*) and using EXPLAIN.

Sean


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On May 18, 2006, at 8:22 AM, Verena Ruff wrote:

>> An
>> alternative to using the LIMIT clause is to use a cursor, but the
>> ability to
>> do so depends on the environment in which you are working. In a web
>> environment, cursors are not useful given the stateless nature of
>> the web
>> interface. Cursors are explained in the Docs.
>>
> The queries are for a webpage, so coursers won't be usefull.

A cursor still might be useful, but it would only be used for the
current request. You could run the query once and fetch the rows you
want to display from the cursor. Then scan to the end of the cursor
to find out how many rows it has. The MOVE command does this and
returns the number of rows.

You would have to test it, but my guess is this would be faster than
executing the same query twice for the two results you are looking for.

Alternatively, you might skip calculating the true count unless the
user clicks on a separate link. This option could show X rows from
the end of the result set and the count(*) result.


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

John DeSoi schrieb:
> A cursor still might be useful, but it would only be used for the
> current request. You could run the query once and fetch the rows you
> want to display from the cursor. Then scan to the end of the cursor to
> find out how many rows it has. The MOVE command does this and returns
> the number of rows.
thanks for this hint. I'll test it.

Regards,
Verena

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster