Q
Problem solve Get help with specific problems with your technologies, process and projects.

Result set row count along with query results

Can I get both the result of the query and the number of rows returned in a single query?

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


This was last published in July 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close