Re: Way to use count() and LIMIT?

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)---------------------------
> TIP 3: 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