Way to use count() and LIMIT?

Sure, just declare a cursor. Here's a simple one that I use:

DECLARE raw_data CURSOR FOR
SELECT
(SELECT pc FROM curpack1 WHERE curpack1.dt <= caseweights1.dt
ORDER BY curpack1.dt DESC LIMIT 1) AS "pc",
dt::date AS "date",
dt::time AS "time",
weight AS "weight"
FROM caseweights1
WHERE dt >= '%s' AND
dt < '%s'
ORDER BY dt;

Then you simply fetch from this cursor (like so):

FETCH FORWARD 20 IN raw_data;

And you close it with a simple:

CLOSE raw_data;

Jason

Joe Koenig <joe@jwebmedia.com> writes:

You will also need to do a select first to get the total count. You can
store it in a var then pass it back to the user for each 20 or whatever
amount (so each time they know total) or pass it once, then create cursor.

You can also use LIMIT with OFFSET to do a simple select each time for 20 at
a time.

-----Original Message-----
From: Jason Earl [mailto:jason.earl@simplot.com]
Sent: Tuesday, December 18, 2001 12:27 PM
To: joe@jwebmedia.com
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Way to use count() and LIMIT?

Sure, just declare a cursor. Here's a simple one that I use:

DECLARE raw_data CURSOR FOR
SELECT
(SELECT pc FROM curpack1 WHERE curpack1.dt <= caseweights1.dt
ORDER BY curpack1.dt DESC LIMIT 1) AS "pc",
dt::date AS "date",
dt::time AS "time",
weight AS "weight"
FROM caseweights1
WHERE dt >= '%s' AND
dt < '%s'
ORDER BY dt;

Then you simply fetch from this cursor (like so):

FETCH FORWARD 20 IN raw_data;

And you close it with a simple:

CLOSE raw_data;

Jason

Joe Koenig <joe@jwebmedia.com> writes:

Is there a way to structure a query so you can only run 1 query, get the
full number of rows that would be returned, but then use LIMIT to step
through in groups of 20? For example, a search in my CD's/Rock section
will return 53,000 results. I want to give the user the number of total
results, but also use LIMIT to go through 20 at a time? Does this
require 2 queries? Thanks,

Joe

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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

http://archives.postgresql.org

I'd be surprised if a cursor is the solution you want. A cursor is good if
you want to "scan" through the rows one at a time via SQL rather than a
client software program (e.g. PHP with ADOdb or something). If you wrote a
function for your DB and needed to access data from a result of a query one
row at a time (for calculating running totals or something -- bad example,
but the point is the calculations need to be done _on_ the DB not the
client) then a cursor is the way to go.

Another thing about cursors is that they tie up the DB resources while they
are open. And since I imagine you are wanting to have the client indicate
when they want to scroll through the next 20 (another app/web request) that
couldn't be done "inside" the DB with a cursor.

By the way, even if you did use the cursor you'd still need to query the
table first for the total count before accessing 20 rows at a time, so
that's a moot point.

Also, I'd be surprised if requesting the total count of rows in a table were
really a hit at all (especially with no WHERE clause -- in that case the
count is probably stored somewhere anyway and won't need to be calculated
dynamically, again, just a guess, but probably true).

Hope that helps,

Mike

-----Original Message-----
From: Joe Koenig [mailto:joe@jwebmedia.com]
Sent: Tuesday, December 18, 2001 2:29 PM
To: SHELTON,MICHAEL (Non-HP-Boise,ex1)
Cc: 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] Way to use count() and LIMIT?

I was currently using a LIMIT and OFFSET to move through 20 at a time. I
need to know the total for 2 reasons:

1) To display it to the user
2) So my script knows whether or not to put a next button.

I was hoping I could avoid 2 queries. Is the best way to do this to just
use LIMIT and OFFSET in one query and just do a count() in the first?
Does using a cursor offer any benefit over the LIMIT and OFFSET method?
Thanks,

Joe

"SHELTON,MICHAEL (Non-HP-Boise,ex1)" wrote:

You will also need to do a select first to get the total count. You can
store it in a var then pass it back to the user for each 20 or whatever
amount (so each time they know total) or pass it once, then create cursor.

You can also use LIMIT with OFFSET to do a simple select each time for 20

at

I was currently using a LIMIT and OFFSET to move through 20 at a time. I
need to know the total for 2 reasons:

1) To display it to the user
2) So my script knows whether or not to put a next button.

I was hoping I could avoid 2 queries. Is the best way to do this to just
use LIMIT and OFFSET in one query and just do a count() in the first?
Does using a cursor offer any benefit over the LIMIT and OFFSET method? Thanks,

Joe

"SHELTON,MICHAEL (Non-HP-Boise,ex1)" wrote:

I think I wasn't clear enough - I need total rows in the result set, not
in the table. Sorry if that wasn't clear. Thanks for the info,

Joe

"SHELTON,MICHAEL (Non-HP-Boise,ex1)" wrote:

Ahh...well, that is different. What type of client are you using to connect
to the DB and get the info?

Mike

-----Original Message-----
From: Joe Koenig [mailto:joe@jwebmedia.com]
Sent: Tuesday, December 18, 2001 2:39 PM
To: SHELTON,MICHAEL (Non-HP-Boise,ex1)
Cc: 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] Way to use count() and LIMIT?

I think I wasn't clear enough - I need total rows in the result set, not
in the table. Sorry if that wasn't clear. Thanks for the info,

Joe

"SHELTON,MICHAEL (Non-HP-Boise,ex1)" wrote:

I'd be surprised if a cursor is the solution you want. A cursor is good

if

you want to "scan" through the rows one at a time via SQL rather than a
client software program (e.g. PHP with ADOdb or something). If you wrote

a

function for your DB and needed to access data from a result of a query

one

row at a time (for calculating running totals or something -- bad example,
but the point is the calculations need to be done _on_ the DB not the
client) then a cursor is the way to go.

Another thing about cursors is that they tie up the DB resources while

they

are open. And since I imagine you are wanting to have the client indicate
when they want to scroll through the next 20 (another app/web request)

that

couldn't be done "inside" the DB with a cursor.

By the way, even if you did use the cursor you'd still need to query the
table first for the total count before accessing 20 rows at a time, so
that's a moot point.

Also, I'd be surprised if requesting the total count of rows in a table

were

really a hit at all (especially with no WHERE clause -- in that case the
count is probably stored somewhere anyway and won't need to be calculated
dynamically, again, just a guess, but probably true).

Hope that helps,

Mike

-----Original Message-----
From: Joe Koenig [mailto:joe@jwebmedia.com]
Sent: Tuesday, December 18, 2001 2:29 PM
To: SHELTON,MICHAEL (Non-HP-Boise,ex1)
Cc: 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] Way to use count() and LIMIT?

I was currently using a LIMIT and OFFSET to move through 20 at a time. I
need to know the total for 2 reasons:

1) To display it to the user
2) So my script knows whether or not to put a next button.

I was hoping I could avoid 2 queries. Is the best way to do this to just
use LIMIT and OFFSET in one query and just do a count() in the first?
Does using a cursor offer any benefit over the LIMIT and OFFSET method?
Thanks,

Joe

"SHELTON,MICHAEL (Non-HP-Boise,ex1)" wrote:

You will also need to do a select first to get the total count. You can
store it in a var then pass it back to the user for each 20 or whatever
amount (so each time they know total) or pass it once, then create

cursor.

You can also use LIMIT with OFFSET to do a simple select each time for

20

at

a time.

-----Original Message-----
From: Jason Earl [mailto:jason.earl@simplot.com]
Sent: Tuesday, December 18, 2001 12:27 PM
To: joe@jwebmedia.com
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Way to use count() and LIMIT?

Sure, just declare a cursor. Here's a simple one that I use:

DECLARE raw_data CURSOR FOR
SELECT
(SELECT pc FROM curpack1 WHERE curpack1.dt <= caseweights1.dt
ORDER BY curpack1.dt DESC LIMIT 1) AS "pc",
dt::date AS "date",
dt::time AS "time",
weight AS "weight"
FROM caseweights1
WHERE dt >= '%s' AND
dt < '%s'
ORDER BY dt;

Then you simply fetch from this cursor (like so):

FETCH FORWARD 20 IN raw_data;

And you close it with a simple:

CLOSE raw_data;

Jason

Joe Koenig <joe@jwebmedia.com> writes:

Is there a way to structure a query so you can only run 1 query, get

the

full number of rows that would be returned, but then use LIMIT to step
through in groups of 20? For example, a search in my CD's/Rock section
will return 53,000 results. I want to give the user the number of

total

results, but also use LIMIT to go through 20 at a time? Does this
require 2 queries? Thanks,

Joe

---------------------------(end of

broadcast)---------------------------

TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to

majordomo@postgresql.org)

I see two options but they may not be the correct ones but here goes.

1) Either put a subselect in your select clause that gets the number of
rows

2) Create a plpgsql function that returns the number of rows and put it in
your select clause.

Both add a little more load to your query but they will give you the
answers. Use explain to figure out which one is less intensive

My 2 cents

Darren

Darren Ferguson
Software Engineer
Openband

On Tue, 18 Dec 2001, Joe Koenig wrote:

PHP 4.1.0 is doing the db queries. Thanks,

Joe

"SHELTON,MICHAEL (Non-HP-Boise,ex1)" wrote:

Sorry I haven't been able to get back to you -- busy at work.

One last comment to try is in MS SQL you can get the number of rows returned
via @@ROW_COUNT -- however I couldn't find the equivalent for PG SQL
(doesn't mean there isn't one).

Anyone else know what might work here? The important detail is wanting to
get a number of rows in the _result_ not necessarily the table.

Mike

-----Original Message-----
From: Joe Koenig [mailto:joe@jwebmedia.com]
Sent: Tuesday, December 18, 2001 3:54 PM
To: SHELTON,MICHAEL (Non-HP-Boise,ex1)
Cc: 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] Way to use count() and LIMIT?

PHP 4.1.0 is doing the db queries. Thanks,

Joe

"SHELTON,MICHAEL (Non-HP-Boise,ex1)" wrote:

Ahh...well, that is different. What type of client are you using to

connect

to the DB and get the info?

Mike

-----Original Message-----
From: Joe Koenig [mailto:joe@jwebmedia.com]
Sent: Tuesday, December 18, 2001 2:39 PM
To: SHELTON,MICHAEL (Non-HP-Boise,ex1)
Cc: 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] Way to use count() and LIMIT?

I think I wasn't clear enough - I need total rows in the result set, not
in the table. Sorry if that wasn't clear. Thanks for the info,

Joe

"SHELTON,MICHAEL (Non-HP-Boise,ex1)" wrote:

I'd be surprised if a cursor is the solution you want. A cursor is good

if

you want to "scan" through the rows one at a time via SQL rather than a
client software program (e.g. PHP with ADOdb or something). If you

wrote

a

function for your DB and needed to access data from a result of a query

one

row at a time (for calculating running totals or something -- bad

example,

but the point is the calculations need to be done _on_ the DB not the
client) then a cursor is the way to go.

Another thing about cursors is that they tie up the DB resources while

they

are open. And since I imagine you are wanting to have the client

indicate

when they want to scroll through the next 20 (another app/web request)

that

couldn't be done "inside" the DB with a cursor.

By the way, even if you did use the cursor you'd still need to query the
table first for the total count before accessing 20 rows at a time, so
that's a moot point.

Also, I'd be surprised if requesting the total count of rows in a table

were

really a hit at all (especially with no WHERE clause -- in that case the
count is probably stored somewhere anyway and won't need to be

calculated

dynamically, again, just a guess, but probably true).

Hope that helps,

Mike

-----Original Message-----
From: Joe Koenig [mailto:joe@jwebmedia.com]
Sent: Tuesday, December 18, 2001 2:29 PM
To: SHELTON,MICHAEL (Non-HP-Boise,ex1)
Cc: 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] Way to use count() and LIMIT?

I was currently using a LIMIT and OFFSET to move through 20 at a time. I
need to know the total for 2 reasons:

1) To display it to the user
2) So my script knows whether or not to put a next button.

I was hoping I could avoid 2 queries. Is the best way to do this to just
use LIMIT and OFFSET in one query and just do a count() in the first?
Does using a cursor offer any benefit over the LIMIT and OFFSET method?
Thanks,

Joe

"SHELTON,MICHAEL (Non-HP-Boise,ex1)" wrote:

You will also need to do a select first to get the total count. You

can

store it in a var then pass it back to the user for each 20 or

whatever

amount (so each time they know total) or pass it once, then create

cursor.

You can also use LIMIT with OFFSET to do a simple select each time for

20

at

a time.

-----Original Message-----
From: Jason Earl [mailto:jason.earl@simplot.com]
Sent: Tuesday, December 18, 2001 12:27 PM
To: joe@jwebmedia.com
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Way to use count() and LIMIT?

Sure, just declare a cursor. Here's a simple one that I use:

DECLARE raw_data CURSOR FOR
SELECT
(SELECT pc FROM curpack1 WHERE curpack1.dt <= caseweights1.dt
ORDER BY curpack1.dt DESC LIMIT 1) AS "pc",
dt::date AS "date",
dt::time AS "time",
weight AS "weight"
FROM caseweights1
WHERE dt >= '%s' AND
dt < '%s'
ORDER BY dt;

Then you simply fetch from this cursor (like so):

FETCH FORWARD 20 IN raw_data;

And you close it with a simple:

CLOSE raw_data;

Jason

Joe Koenig <joe@jwebmedia.com> writes:

Is there a way to structure a query so you can only run 1 query, get

the

full number of rows that would be returned, but then use LIMIT to

step

through in groups of 20? For example, a search in my CD's/Rock

section

will return 53,000 results. I want to give the user the number of

total

results, but also use LIMIT to go through 20 at a time? Does this
require 2 queries? Thanks,

Joe

---------------------------(end of

broadcast)---------------------------

TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to

majordomo@postgresql.org)

---------------------------(end of

broadcast)---------------------------

TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of

broadcast)---------------------------

"SHELTON,MICHAEL (Non-HP-Boise,ex1)" <michael_shelton@non.hp.com> writes:

Sorry I haven't been able to get back to you -- busy at work.

One last comment to try is in MS SQL you can get the number of rows returned
via @@ROW_COUNT -- however I couldn't find the equivalent for PG SQL
(doesn't mean there isn't one).

Anyone else know what might work here? The important detail is wanting to
get a number of rows in the _result_ not necessarily the table.

libpq gives you this information via the PQntuples() function.
Whether PHP gives you access to that info I don't know--see your docs.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

see http://www.php.net/manual/en/function.pg-numrows.php

Still, this isn't what you want I'd say.. That just gives you the number of
rows returned by a query -- a query that uses LIMIT X return X or less
records, so that's not the most that pg_numrows() will ever give you is X...

I've run into this before when I did some search engine work with PG, it's a
pain but I've found no workaround... I ended up having to do two queries,
one a count() and one to return the records...

-Mitch

----- Original Message -----
From: "Doug McNaught" <doug@wireboard.com>
To: "SHELTON,MICHAEL (Non-HP-Boise,ex1)" <michael_shelton@non.hp.com>
Cc: <joe@jwebmedia.com>; <pgsql-general@postgresql.org>
Sent: Tuesday, December 18, 2001 4:49 PM
Subject: Re: [GENERAL] Way to use count() and LIMIT?

"SHELTON,MICHAEL (Non-HP-Boise,ex1)" <michael_shelton@non.hp.com> writes:

Sorry I haven't been able to get back to you -- busy at work.

One last comment to try is in MS SQL you can get the number of rows

returned

via @@ROW_COUNT -- however I couldn't find the equivalent for PG SQL
(doesn't mean there isn't one).

Anyone else know what might work here? The important detail is wanting

to

On Tue, Dec 18, 2001 at 05:24:39PM -0700, Mitch Vincent wrote:

see http://www.php.net/manual/en/function.pg-numrows.php

Still, this isn't what you want I'd say.. That just gives you the number of
rows returned by a query -- a query that uses LIMIT X return X or less
records, so that's not the most that pg_numrows() will ever give you is X...

I've run into this before when I did some search engine work with PG, it's a
pain but I've found no workaround... I ended up having to do two queries,
one a count() and one to return the records...

Well, there are conflicting requirements. By using LIMIT the database can
choose a plan better suited to giving a few results quickly but would take
forever if it had to do the whole table.

By saying that you want the total number of rows but only want a few back
you're basically asking the database to execute the entire query and then
toss out most of the result.

I gather the reason you don't actually run the whole query is because the
resultset would be too large? But you still want the database to work out
exactly how many there are.

Maybe just go for an estimate of the total? How big do you expect your
results to be?
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/

I gather the reason you don't actually run the whole query is because the
resultset would be too large? But you still want the database to work out
exactly how many there are.

Not really because it's too large but because there isn't a need.. Imagine
your favorite search engine, you search and the results are displayed "X
Matches, displaying matches 1 to 10"... Same thing here -- at least that's
what my need was... The query executed could get really stout (there were
over 60 searchable fields across a variety of tables with hundreds of
thousands of records in some) so executing the count() query, then the other
certainly added the overhead... A cursor wasn't usable in this situation
because the user could chose to search once, having only ten results
displayed of 10,000 and leave the page (thus leaving me with an open
cursor).....

But again, I see no solution to the above problem and understandably so, if
you LIMIT a result set, you *limit* it -- asking it to contradict itself
doesn't make any sense so I never complained that it wasn't possible :-)

-Mitch

On Tue, Dec 18, 2001 at 09:19:46PM -0700, Mitch Vincent wrote:

I gather the reason you don't actually run the whole query is because the
resultset would be too large? But you still want the database to work out
exactly how many there are.

Not really because it's too large but because there isn't a need.. Imagine
your favorite search engine, you search and the results are displayed "X
Matches, displaying matches 1 to 10"... Same thing here -- at least that's
what my need was... The query executed could get really stout (there were
over 60 searchable fields across a variety of tables with hundreds of
thousands of records in some) so executing the count() query, then the other
certainly added the overhead... A cursor wasn't usable in this situation
because the user could chose to search once, having only ten results
displayed of 10,000 and leave the page (thus leaving me with an open
cursor).....

I'd be tempted to simply limit to 100 or so and if you get over 100, say
"matched over 100 documents". As for the cursor, I don't think google
remembers your results while switching between pages. If you switch back and
forth between pages I'm pretty sure the results change from time to time.

If you think about the number is servers they have, caching query results
would be almost as complicated as the searching itself.
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/

This may be a long shot but you could try a view that has one field and
that field would be the count(*) of the table i don't think this would add
to much overhead.

Just a thought

Darren

Darren Ferguson
Software Engineer
Openband

On Wed, 19 Dec 2001, Martijn van Oosterhout wrote:

This is exactly right. The table I'm searching has 220,000 records right
now, and growing. The "Rock" category within "CD's" will return over
53,000 results. The count isn't so much for display, as for knowing
whether or not to put a "next" button on the page. I don't want to
always have next buttons, but not always have more results. There is
currently the option to browse through results based on the first letter
of the result, which I guess I could make as the 3rd step and not run
the query that currently returns 53,000 results until they narrow it
down more. I wasn't really expecting this to be possible, but PG does so
many other things that I didn't think was possible, so I thought I'd ask :)

Joe

Mitch Vincent wrote:

On Wed, Dec 19, 2001 at 08:13:50AM -0600, Joe Koenig wrote:

This is exactly right. The table I'm searching has 220,000 records right
now, and growing. The "Rock" category within "CD's" will return over
53,000 results. The count isn't so much for display, as for knowing
whether or not to put a "next" button on the page. I don't want to
always have next buttons, but not always have more results. There is
currently the option to browse through results based on the first letter
of the result, which I guess I could make as the 3rd step and not run
the query that currently returns 53,000 results until they narrow it
down more. I wasn't really expecting this to be possible, but PG does so
many other things that I didn't think was possible, so I thought I'd ask :)

But that's easy. Simply ask the database for one more record that you're
going to display. If you get that extra record, then display the next
button, otherwise don't. The count is irrelevent.

--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/

That's so rediculously simple it hurts...I'm an idiot...Thank you VERY much.

Joe

Martijn van Oosterhout wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Simply ask the database for one more record that you're going to
display. If you get that extra record, then display the next button,
otherwise don't. The count is irrelevent

Great idea. If you are using DBI, you can even do a $sth->rows()
call to avoid the overhead of grabbing the data via a fetch.
(unless you are using fetchall/selectall)

Still, it seems that there should be a way to grab the information,
especially if you have an "ORDER BY" clause in your query - after
all, that means at some point, postgres knows how many records
matched before it ORDERS and LIMITS them.

Something like this:

SELECT pg_get_totalrows, artist, title, year
FROM giantcdcollection
WHERE genre = 'Rock'
ORDER BY artist, title
LIMIT 21
OFFSET 200

should be possible with no slow down of query time, I would think.
Inefficient in that it returns the same number 21 times, but
a bargain compared to the current way to do it (separate count query).

Greg Sabino Mullane
greg@turnstep.com
PGP Key: 0x14964AC8 200112191051
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE8ILggvJuQZxSWSsgRAny6AKCG/DrD2dhft6/kwjiHJ5a5jPwvFQCfeoHJ
Ej56nl3x5+snq9wynedCfUo=
=LFIM
-----END PGP SIGNATURE-----