SQL - Return limited number of rows, but full row count

Question

Scenario: I need to pull information out of a Visual FoxPro database; however, running large queries against this has a tendency to lock the system up. To resolve this, we put limits in place that cancelled the query if it ran past a certain amount of time, and limited the number of rows it would return.

Is there a way to have a query with "SELECT TOP ###", but also return the actual number of rows found through the statement? Or is the only way to run the query twice? (reason being we can still have the query run through, but inform the user of what's going on. i.e. "First ### displayed of ### found items").

My initial trial was to just simply add a "COUNT(*)" to the select portion of the statement, but that didn't quite accomplish what I was looking for (it returned the correct number of rows, but only returned one row for the rest of the data).

Answer 1

If I understand the question correctly, you could do a subselect, but that will mean you call the count SQL for every row returned:

select top 10 field1, field2, (select count(*) from table) as totalrows from table

That will give you the top 10 rows with an extra column in each called totalrows, containing the count of all the rows in the table.

Personally though, I'd just run a separate query to get the top n rows and the count.

Answer 2

You would need to run 2 separate SELECTs. One to retrieve the COUNT of rows returned by the query and then to return the subset of records for a particular page.

You could optimise this by only retrieving the total COUNT once, when the first "page" is retrieved (i.e. don't do the entire count for subsequent pages)