Обсуждение: Way to use count() and LIMIT?
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
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)
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
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
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
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
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
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 >
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
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
"SHELTON,MICHAEL (Non-HP-Boise,ex1)" <michael_shelton@non.hp.com> writes: > 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. libpq gives you this information via the PQntuples() function. Whether PHP gives you access to that info I don't know--see your docs. -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863
see http://www.php.net/manual/en/function.pg-numrows.php Still, this isn't what you want I'd say.. That just gives you the number of rows returned by a query -- a query that uses LIMIT X return X or less records, so that's not the most that pg_numrows() will ever give you is X... I've run into this before when I did some search engine work with PG, it's a pain but I've found no workaround... I ended up having to do two queries, one a count() and one to return the records... -Mitch ----- Original Message ----- From: "Doug McNaught" <doug@wireboard.com> To: "SHELTON,MICHAEL (Non-HP-Boise,ex1)" <michael_shelton@non.hp.com> Cc: <joe@jwebmedia.com>; <pgsql-general@postgresql.org> Sent: Tuesday, December 18, 2001 4:49 PM Subject: Re: [GENERAL] Way to use count() and LIMIT? > "SHELTON,MICHAEL (Non-HP-Boise,ex1)" <michael_shelton@non.hp.com> writes: > > > 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. > > libpq gives you this information via the PQntuples() function. > Whether PHP gives you access to that info I don't know--see your docs. > > -Doug > -- > Let us cross over the river, and rest under the shade of the trees. > --T. J. Jackson, 1863 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
On Tue, Dec 18, 2001 at 05:24:39PM -0700, Mitch Vincent wrote: > see http://www.php.net/manual/en/function.pg-numrows.php > > Still, this isn't what you want I'd say.. That just gives you the number of > rows returned by a query -- a query that uses LIMIT X return X or less > records, so that's not the most that pg_numrows() will ever give you is X... > > I've run into this before when I did some search engine work with PG, it's a > pain but I've found no workaround... I ended up having to do two queries, > one a count() and one to return the records... Well, there are conflicting requirements. By using LIMIT the database can choose a plan better suited to giving a few results quickly but would take forever if it had to do the whole table. By saying that you want the total number of rows but only want a few back you're basically asking the database to execute the entire query and then toss out most of the result. I gather the reason you don't actually run the whole query is because the resultset would be too large? But you still want the database to work out exactly how many there are. Maybe just go for an estimate of the total? How big do you expect your results to be? -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Terrorists can only take my life. Only my government can take my freedom.
> I gather the reason you don't actually run the whole query is because the > resultset would be too large? But you still want the database to work out > exactly how many there are. Not really because it's too large but because there isn't a need.. Imagine your favorite search engine, you search and the results are displayed "X Matches, displaying matches 1 to 10"... Same thing here -- at least that's what my need was... The query executed could get really stout (there were over 60 searchable fields across a variety of tables with hundreds of thousands of records in some) so executing the count() query, then the other certainly added the overhead... A cursor wasn't usable in this situation because the user could chose to search once, having only ten results displayed of 10,000 and leave the page (thus leaving me with an open cursor)..... But again, I see no solution to the above problem and understandably so, if you LIMIT a result set, you *limit* it -- asking it to contradict itself doesn't make any sense so I never complained that it wasn't possible :-) -Mitch
On Tue, Dec 18, 2001 at 09:19:46PM -0700, Mitch Vincent wrote: > > I gather the reason you don't actually run the whole query is because the > > resultset would be too large? But you still want the database to work out > > exactly how many there are. > > Not really because it's too large but because there isn't a need.. Imagine > your favorite search engine, you search and the results are displayed "X > Matches, displaying matches 1 to 10"... Same thing here -- at least that's > what my need was... The query executed could get really stout (there were > over 60 searchable fields across a variety of tables with hundreds of > thousands of records in some) so executing the count() query, then the other > certainly added the overhead... A cursor wasn't usable in this situation > because the user could chose to search once, having only ten results > displayed of 10,000 and leave the page (thus leaving me with an open > cursor)..... I'd be tempted to simply limit to 100 or so and if you get over 100, say "matched over 100 documents". As for the cursor, I don't think google remembers your results while switching between pages. If you switch back and forth between pages I'm pretty sure the results change from time to time. If you think about the number is servers they have, caching query results would be almost as complicated as the searching itself. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Terrorists can only take my life. Only my government can take my freedom.
This may be a long shot but you could try a view that has one field and that field would be the count(*) of the table i don't think this would add to much overhead. Just a thought Darren Darren Ferguson Software Engineer Openband On Wed, 19 Dec 2001, Martijn van Oosterhout wrote: > On Tue, Dec 18, 2001 at 09:19:46PM -0700, Mitch Vincent wrote: > > > I gather the reason you don't actually run the whole query is because the > > > resultset would be too large? But you still want the database to work out > > > exactly how many there are. > > > > Not really because it's too large but because there isn't a need.. Imagine > > your favorite search engine, you search and the results are displayed "X > > Matches, displaying matches 1 to 10"... Same thing here -- at least that's > > what my need was... The query executed could get really stout (there were > > over 60 searchable fields across a variety of tables with hundreds of > > thousands of records in some) so executing the count() query, then the other > > certainly added the overhead... A cursor wasn't usable in this situation > > because the user could chose to search once, having only ten results > > displayed of 10,000 and leave the page (thus leaving me with an open > > cursor)..... > > I'd be tempted to simply limit to 100 or so and if you get over 100, say > "matched over 100 documents". As for the cursor, I don't think google > remembers your results while switching between pages. If you switch back and > forth between pages I'm pretty sure the results change from time to time. > > If you think about the number is servers they have, caching query results > would be almost as complicated as the searching itself. > -- > Martijn van Oosterhout <kleptog@svana.org> > http://svana.org/kleptog/ > > Terrorists can only take my life. Only my government can take my freedom. > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
This is exactly right. The table I'm searching has 220,000 records right now, and growing. The "Rock" category within "CD's" will return over 53,000 results. The count isn't so much for display, as for knowing whether or not to put a "next" button on the page. I don't want to always have next buttons, but not always have more results. There is currently the option to browse through results based on the first letter of the result, which I guess I could make as the 3rd step and not run the query that currently returns 53,000 results until they narrow it down more. I wasn't really expecting this to be possible, but PG does so many other things that I didn't think was possible, so I thought I'd ask :) Joe Mitch Vincent wrote: > > > I gather the reason you don't actually run the whole query is because the > > resultset would be too large? But you still want the database to work out > > exactly how many there are. > > Not really because it's too large but because there isn't a need.. Imagine > your favorite search engine, you search and the results are displayed "X > Matches, displaying matches 1 to 10"... Same thing here -- at least that's > what my need was... The query executed could get really stout (there were > over 60 searchable fields across a variety of tables with hundreds of > thousands of records in some) so executing the count() query, then the other > certainly added the overhead... A cursor wasn't usable in this situation > because the user could chose to search once, having only ten results > displayed of 10,000 and leave the page (thus leaving me with an open > cursor)..... > > But again, I see no solution to the above problem and understandably so, if > you LIMIT a result set, you *limit* it -- asking it to contradict itself > doesn't make any sense so I never complained that it wasn't possible :-) > > -Mitch > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On Wed, Dec 19, 2001 at 08:13:50AM -0600, Joe Koenig wrote: > This is exactly right. The table I'm searching has 220,000 records right > now, and growing. The "Rock" category within "CD's" will return over > 53,000 results. The count isn't so much for display, as for knowing > whether or not to put a "next" button on the page. I don't want to > always have next buttons, but not always have more results. There is > currently the option to browse through results based on the first letter > of the result, which I guess I could make as the 3rd step and not run > the query that currently returns 53,000 results until they narrow it > down more. I wasn't really expecting this to be possible, but PG does so > many other things that I didn't think was possible, so I thought I'd ask :) But that's easy. Simply ask the database for one more record that you're going to display. If you get that extra record, then display the next button, otherwise don't. The count is irrelevent. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Terrorists can only take my life. Only my government can take my freedom.
That's so rediculously simple it hurts...I'm an idiot...Thank you VERY much. Joe Martijn van Oosterhout wrote: > > On Wed, Dec 19, 2001 at 08:13:50AM -0600, Joe Koenig wrote: > > This is exactly right. The table I'm searching has 220,000 records right > > now, and growing. The "Rock" category within "CD's" will return over > > 53,000 results. The count isn't so much for display, as for knowing > > whether or not to put a "next" button on the page. I don't want to > > always have next buttons, but not always have more results. There is > > currently the option to browse through results based on the first letter > > of the result, which I guess I could make as the 3rd step and not run > > the query that currently returns 53,000 results until they narrow it > > down more. I wasn't really expecting this to be possible, but PG does so > > many other things that I didn't think was possible, so I thought I'd ask :) > > But that's easy. Simply ask the database for one more record that you're > going to display. If you get that extra record, then display the next > button, otherwise don't. The count is irrelevent. > > -- > Martijn van Oosterhout <kleptog@svana.org> > http://svana.org/kleptog/ > > Terrorists can only take my life. Only my government can take my freedom.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > Simply ask the database for one more record that you're going to > display. If you get that extra record, then display the next button, > otherwise don't. The count is irrelevent Great idea. If you are using DBI, you can even do a $sth->rows() call to avoid the overhead of grabbing the data via a fetch. (unless you are using fetchall/selectall) Still, it seems that there should be a way to grab the information, especially if you have an "ORDER BY" clause in your query - after all, that means at some point, postgres knows how many records matched before it ORDERS and LIMITS them. Something like this: SELECT pg_get_totalrows, artist, title, year FROM giantcdcollection WHERE genre = 'Rock' ORDER BY artist, title LIMIT 21 OFFSET 200 should be possible with no slow down of query time, I would think. Inefficient in that it returns the same number 21 times, but a bargain compared to the current way to do it (separate count query). Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200112191051 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iD8DBQE8ILggvJuQZxSWSsgRAny6AKCG/DrD2dhft6/kwjiHJ5a5jPwvFQCfeoHJ Ej56nl3x5+snq9wynedCfUo= =LFIM -----END PGP SIGNATURE-----
"Greg Sabino Mullane" <greg@turnstep.com> writes:
> Still, it seems that there should be a way to grab the information,
> especially if you have an "ORDER BY" clause in your query - after
> all, that means at some point, postgres knows how many records
> matched before it ORDERS and LIMITS them.
Not necessarily; it could be using an indexscan to do the ordering.
regards, tom lane