EXPERT RESPONSE
Every database has a facility to return a status code
to indicate whether a query was successful. Along with this,
the database also usually returns the number of rows processed.
For example, if you run a SELECT query in Oracle, it will
say "nn rows selected." Microsoft's SQL/Server will say
"nn rows affected" (even though the rows aren't really
"affected" by a SELECT).
If you are using an application with scripting capabilities,
like a Web interface developed with ASP or PHP or ColdFusion,
for example, then this row count is also available for every query
you run.
<cfquery name="getrecs" datasource="#yourdb#">
select foo, bar
from yourtable
where baz = "quux"
</cfquery>
<cfoutput>
<p>
The number of records is: #getrecs.RecordCount#
</p>
</cfoutput>
You can also retrieve the number of records via SQL,
although it's a bit of a
hack,
because in order to do it in one query,
you have stretch the definition of "one query"
to cover a UNION with two subqueries.
( select 1 as rowtype
, foo
, bar
from yourtable
where baz = "quux" )
union all
( select 2
, count(*)
, sum(bar)
from yourtable
where baz = "quux" )
order by rowtype, foo
A good optimizer should
be able to detect the same WHERE clause
and not make two complete passes of the table.
Note that the rowtype column is used
both to distinguish "detail" rows from the "total" row,
and also as the major sort field,
so that the total row comes last. The count of rows
is returned in the foo column, and just for fun,
the sum of the bar column values is returned
in the bar column.
So this "hack" is actually a good hack,
in that it can be used for other aggregates like SUM()
for which no standard mechanism
(like the number of rows processed) is provided.
For More Information
|
