Re: Can an SQL common table expression be used for many different selects?

Sorry that you're getting so many responses that essentially are
saying the same thing regarding GET DIAGNOSTICS. (Part of this is that
different people receive/check this mailing list more quickly or
slowly, so you get some people who respond "late" but are nevertheless
responding as early as they can and haven't seen other responses. Part
of it is just that in any long thread, the earlier responses get
forgotten/lost even if they have been read.)

So, anyway, what you are really after (fast response time via paging,
but also wanting to know effectively the sum of all the page sizes up
front) is an age-old problem. I don't think, conceptually speaking,
there is any way around either (1) having a dedicated, separate query
to get the full count, or (2) fetching the entire set in the first
place and feeding it to the user page by page.

There are variations on those, but they would involve views or
temporary tables or perhaps other solutions, all of which you seem to
want to avoid. But if the performance is what matters most, I guess it
basically comes down to testing the various schemes and seeing which
one the SQL engine happens to handle the fastest, and then going with
that, elegance be damned.

Honestly, if you were to just include copious comments explaining
"hey, yeah, this code is ugly, but we need it to run as fast as
possible, and this is the way that we've found runs the fastest", to
me that is perfectly fine.

John Y.