Using TOP and count in single query

  • AJ07

    Ten Centuries

    Points: 1334

    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:

  • Dave Ballantyne

    SSC-Dedicated

    Points: 33556

    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

    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: 18229

    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: 33556

    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: 53197

    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

    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: 53197

    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

    You can try this

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

     

     

     

  • drew.allen

    SSC Guru

    Points: 76493

    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

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

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