Re: [GENERAL] Re: is this possible? it should be!
On Mon, 20 Aug 2001 [EMAIL PROTECTED] wrote: > On Mon, Aug 20, 2001 at 04:56:29PM -0700, Tony Reina wrote: > > Perhaps GROUP BY will get you where you want to go: > > > > select count(*), a, b, c from a where d=2 group by a, b, c order by e limit 10; > > > > > > Here count(*) doesn't give total count i.e. grand total > count if there is no "limit." > > > What would be nice is if pg would return 10 rows but declare > at the bottom of the display to give total rows number. This way > DBI can just do > $n=$sql->total_rows; > or something like that. I think it requires a major > hack on postgres? No? I don't think it will be > any additional cpu cost to return total number of rows > since sorting needs to know all rows and hence > total number of rows > I don;'t know if it's considered now, but that query (the original) doesn't necessarily require a sort step. With an index on (d,e) I think you could do the query using the index for both the constraint (d=2) and the ordering stopping when you have 10 rows. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Re: is this possible? it should be!
On Mon, Aug 20, 2001 at 04:56:29PM -0700, Tony Reina wrote: > Perhaps GROUP BY will get you where you want to go: > > select count(*), a, b, c from a where d=2 group by a, b, c order by e limit 10; > > Here count(*) doesn't give total count i.e. grand total count if there is no "limit." What would be nice is if pg would return 10 rows but declare at the bottom of the display to give total rows number. This way DBI can just do $n=$sql->total_rows; or something like that. I think it requires a major hack on postgres? No? I don't think it will be any additional cpu cost to return total number of rows since sorting needs to know all rows and hence total number of rows ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Re: is this possible? it should be!
> select a,b,c from a where d=2 order by e limit 10; > select count(*) from a where d=2; > > The point is that I want to know the total number > of matches and I also want to use "limit". And > I don't want to do two queries. > Perhaps GROUP BY will get you where you want to go: select count(*), a, b, c from a where d=2 group by a, b, c order by e limit 10; -Tony ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Re: is this possible? it should be!
On Mon, Aug 20, 2001 at 07:44:56AM -0500, Bruno Wolff III <[EMAIL PROTECTED]> wrote: > Shouldn't: > > select a,b,c, count(*) from a where d=2 order by e limit 10; > > > do what you want? Upon further review, I see that this construct isn't valid. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Re: is this possible? it should be!
On Sun, Aug 19, 2001 at 01:49:00PM -0400, [EMAIL PROTECTED] wrote: > Hello > > I have statements (highly simplified just to get > the point across) like > > select a,b,c from a where d=2 order by e limit 10; > > Now I think that because of "order by" the above query > already "knows" the result of the below query > > select count(*) from a where d=2; > > The point is that I want to know the total number > of matches and I also want to use "limit". And > I don't want to do two queries. Shouldn't: select a,b,c, count(*) from a where d=2 order by e limit 10; do what you want? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
