# of records and TOP clause

Question

# of records and TOP clause

rgtft

Hall of Fame

Points: 3518
More actions
July 1, 2011 at 12:40 pm

#252945

Is there a way in SQL Server to get the number of records in a SELECT that has a TOP clause. That is the total number of records that match the criteria.
SELECT TOP 50 Col1, Col2
FROM tblMyTable
WHERE Col1 = 'abc'
If a million records fit this WHERE clause criteria, how can I get the number of records of a million rather than 50 -- other than doing a COUNT() in a separate SELECT?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

rgtft (7/1/2011)
Is there a way in SQL Server to get the number of records in a SELECT that has a TOP clause. That is the total number of records that match the criteria.
SELECT TOP 50 Col1, Col2
FROM tblMyTable
WHERE Col1 = 'abc'
If a million records fit this WHERE clause criteria, how can I get the number of records of a million rather than 50 -- other than doing a COUNT() in a separate SELECT?

If you need both the top 50 and the count() then you have to do both.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

rgtft Hall of Fame Points: 3518 More actions · Answer 2

PaulB-TheOneAndOnly (7/1/2011)
If you need both the top 50 and the count() then you have to do both.

That's what I thought; I was just wondering if there was some behind the scenes variable in SQL Server's internal workings that might have the total rows that I wasn't aware of.

Thanks,

Rob

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

rgtft (7/1/2011)
PaulB-TheOneAndOnly (7/1/2011)
If you need both the top 50 and the count() then you have to do both.
That's what I thought; I was just wondering if there was some behind the scenes variable in SQL Server's internal workings that might have the total rows that I wasn't aware of.

Well... in that sense you are correct, information is there in some place because you can see it when tracing a particular query, problem is that I do not think such information is readily available to a normal query in standard circumstances.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 4

You can try something like this:

select top 100 name , CountOfRow = COUNT(*) OVER( PARTITION BY 1)

from sys.columns