Thread: is this possible? it should be!

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.

If it's impossible I would like to know whether
it costs the same to PG if I use it with or
without limit.

If I use DBI and simplified queries look like

$s=$dbh->prepare('select a,b,c from a where d=2 order by e ');
$s->execute();

I get the total number of rows by

$n=$s->rows;

I then use perl to implement "limit"

Thanks in advance for any hints

On Sun, Aug 19, 2001 at 01:49:00PM -0400,
  newsreader@mediaone.net 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?

On Mon, Aug 20, 2001 at 07:44:56AM -0500,
  Bruno Wolff III <bruno@wolff.to> 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.

> 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

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

On Mon, 20 Aug 2001 newsreader@mediaone.net 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.

On Mon, Aug 20, 2001 at 10:30:07PM -0700, Stephan Szabo wrote:
>
>
> 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.

I see.  What I am asking is impossible then.

Thanks.  I will have to go with two queries
approach