Re: Way to use count() and LIMIT?

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)---------------------------
> > > 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html