Re: Way to use count() and LIMIT?

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