Re: Way to use count() and LIMIT? - Mailing list pgsql-general
From | Darren Ferguson |
---|---|
Subject | Re: Way to use count() and LIMIT? |
Date | |
Msg-id | Pine.LNX.4.10.10112181719370.28107-100000@thread.crystalballinc.com Whole thread Raw |
In response to | Re: Way to use count() and LIMIT? (Joe Koenig <joe@jwebmedia.com>) |
List | 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.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 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 >
pgsql-general by date: