Re: Can I get the number of results plus the results with a single query?

On 2022-08-15 08:49:33 -0500, Perry Smith wrote:
> I like to have what I call “baby sitting” messages such as “Completed
> 15 out of 1023”. To do this, I need the number of results a query
> returns but I also need the results.
>
> Currently I’m doing this with two queries such as:
>
> SELECT COUNT(*) FROM table WHERE …. expression …
> SELECT * FROM table WHERE …. expression …
>
> But this requires two queries.

And unless evaluating/transmitting the select expressions is very
expensive the first one is unlikely to be much faster than the second.
So you've just halved your performance.

> Is there a way to do the same thing with just one query somehow?

I don't think there can be. You don't know how many results there will
be until you have evaluated the query, but when you have done that you
already have all the results so displaying the baby sitting messages or
a progress bar becomes pointless.

> I’ve been toying with row_number() and then sort by row_number
> descending and pick off the first row as the total number.

I think that this will usually result in a long pause at the start
(while the query is evaluated) followed by a very quick count up (while
the result is transmitted to the client). Probably not what you want.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"