CODEWITHSUNDEEP
sqlsql-server
kingdango
kingdango

Reputation: 3999

How do you Select TOP x but still get a COUNT of the whole query?

I'm writing a webpage to interactively filter results based on filter criteria as it is specified by the user. I only want to return from SQL the TOP 20 rows but I want to know how many rows met the criteria (Count). I want to be able to tell the user: "here are the top 20 rows matching your criteria, and BTW, there were 2,000 additional rows I'm not showing here".

I know I could simply run the query twice but EWWWW that's expensive and wasteful. How can I achieve what I want without over taxing the database?

Upvotes: 26

Views: 28751

Answers (2)

Mouli Reddy
Mouli Reddy

Reputation: 21

select COUNT(*) from Process_Master where dt_time=(select top 1 (dt_time) from Process_Master order by DT_TIME desc)

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453008

You can use COUNT(*) OVER()

SELECT TOP 20 *, 
       COUNT(*) OVER() AS TotalMatchingRows
FROM master..spt_values
WHERE type='P'
ORDER BY number

Doing two queries may work out more efficient however especially if you have narrower indexes that can be used in determining the matching row count but don't cover the entire SELECT list.

Upvotes: 51

Related Questions