Re: Way to use count() and LIMIT? - Mailing list pgsql-general

From Jason Earl
Subject Re: Way to use count() and LIMIT?
Date
Msg-id 87k7vke3ch.fsf@npa01zz001.simplot.com
Whole thread Raw
In response to Way to use count() and LIMIT?  (Joe Koenig <joe@jwebmedia.com>)
List pgsql-general
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)

pgsql-general by date:

Previous
From: Joe Koenig
Date:
Subject: Way to use count() and LIMIT?
Next
From: "SHELTON,MICHAEL (Non-HP-Boise,ex1)"
Date:
Subject: Re: Way to use count() and LIMIT?