YellowBedwetter YellowBedwetter - 4 years ago 92
SQL Question

Query to display total row count returned, but with a limitted result set

For QA purposes, I need to provide a sample of error records as well the total number of errors records. For background, this sample needs to be limited to ~1000 records since the query results are stored in an Excel recordSet object and output to a text file. This sounds very clunky (and is) but there are reasons for it.

I know I can do:

SELECT TOP 1000
primaryKey
,expectedValue
,actualValue
,totalErrors
FROM errorTable
INNER JOIN (SELECT count(*) as totalErrors FROM errorTable) AS tmp
ON 1 = 1


But I'd like a more efficient way since "errorTable" is actually a subquery that finds all the error records and can get pretty computationally expensive.

Answer Source

Just use window functions:

SELECT TOP 1000 primaryKey, expectedValue, actualValue,
       COUNT(*) OVER () as totalErrors
FROM errorTable;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download