Using TOP and count in single query

Question

Using TOP and count in single query

AJ07

Ten Centuries

Points: 1334
More actions
August 24, 2009 at 6:17 am

#209815

Hi all,
I am trying to use Top and count in 1 query.
select top(100) count(1) from table where name like 'a%'
now if the count < 100 i will use count else will use 100. but i am not getting count > 100 means that is not dependent on top.
can anybody help me on this?
I wish, I wasn't so nice!!!!!!!!!!!!!!!!!!!!!!:cool:

Viewing 10 posts - 1 through 9 (of 9 total)

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Top is processed on the result set , so you wont get the results you are expecting

http://sqlserverpedia.com/blog/sql-server-bloggers/sql-server-%E2%80%93-logical-query-processing-phases-%E2%80%93-order-of-statement-execution/%5B/url%5D

Is this what you are after ?

select case when count(*) >100 then 100 else count(*) end

from sysobjects where name like 'a%'

Clear Sky SQL
My Blog[/url]

AJ07 Ten Centuries Points: 1334 More actions · Answer 2

Thanks Dave,

This is what I am doing right now but I just want to ignore the full table scan, so if after filtering it gives me count = 200 then I want to break the table scan.

Like suppose in case of result set if you select top(100) and no filter is there then query may return more than 100 but using top i get only 100 and if query return less then 100 without top then too I can get same result while applying the top(100).

So this is the thing I am trying......................

Ken McKelvey SSCoach Points: 18842 More actions · Answer 3

DECLARE @t TABLE (t bit)

DECLARE @r int

SET ROWCOUNT 100

INSERT INTO @t SELECT 1 FROM YourTable WHERE [name] LIKE 'a%'

SET @r = @@ROWCOUNT

SET ROWCOUNT 0

PRINT @r

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 4

Hows this ?

with cteResults(Name)

as

(

Select top 100 Name

from sysobjects

where Name like 'a%'

)

Select count(*) from cteResults

Clear Sky SQL
My Blog[/url]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

AJ, is there an ORDER BY in your query by any chance?

I ask because when you SELECT TOP (N) rows without an ORDER BY, the execution plan will show only N rows being selected by the table scan.

However, if you add an ORDER BY, then the entire table must be scanned and sorted before the TOP (N) rows can be determined.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

AJ07 Ten Centuries Points: 1334 More actions · Answer 6

Yes Bob,

I have Order By clause in my query.

Actually I am trying to implement the custom paging in my application and for that I have to fetch the count as well as result set and the Order by column is also passed as a parameter in the procedure.

I have done my job but I want to improve its performance but I am not getting how.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

Do you understand why the ORDER BY compels the execution of a full table scan? The only way I think you could improve on that would be the creation of a covering index for this query.

I am also confused about what you are referring to as the count. I'm sure you know that @@Rowcount will tell you how many rows were returned.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

irwank.it@gmail.com Newbie Points: 1 More actions · Answer 8

You can try this

Select count(1) CNT from (SELECT top 200 * from tblSample) a

drew.allen SSC Guru Points: 76985 More actions · Answer 9

irwank.it@gmail.com wrote:

You can try this
Select count(1) CNT from (SELECT top 200 * from tblSample) a

Are you aware that this thread is a decade old and the original poster may not even be active anymore?

I notice in one of his responses he states that he is trying to implement custom paging. This was before the introduction of OFFSET/FETCH which was specifically introduced for handling paging.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA