From: | Darren Ferguson <darren(at)crystalballinc(dot)com> |
---|---|
To: | Joe Koenig <joe(at)jwebmedia(dot)com> |
Cc: | "SHELTON,MICHAEL (Non-HP-Boise,ex1)" <michael_shelton(at)non(dot)hp(dot)com>, "'pgsql-general(at)postgresql(dot)org'" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Way to use count() and LIMIT? |
Date: | 2001-12-18 22:22:22 |
Message-ID: | Pine.LNX.4.10.10112181719370.28107-100000@thread.crystalballinc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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:
> 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(dot)earl(at)simplot(dot)com]
> > Sent: Tuesday, December 18, 2001 12:27 PM
> > To: joe(at)jwebmedia(dot)com
> > Cc: pgsql-general(at)postgresql(dot)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(at)jwebmedia(dot)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(at)postgresql(dot)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(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2001-12-18 22:23:23 | Re: Too Many Open Files... NetBSD |
Previous Message | Bruce Momjian | 2001-12-18 22:20:04 | Re: Too Many Open Files... NetBSD |