Display COUNT(*) but ONLY TOP 100

  • Comments posted to this topic are about the item Display COUNT(*) but ONLY TOP 100

  • Your article begins with:

    "Let's say you have a result set with 5000 records but you want to display only 100 of them and in the same time to display the count of ALL records (5000)."

    So, how do you display the "5000"? I don't see that anywhere in your example. The article is a bit choppy.

  • You should use ROW_NUMBER() instead of rank. Rank will assign the same number to the first to items in your data set. They are both 1 so they have the same rank. ROW_Number will be unique.

  • Hi Mike1024,

    "how do I display 5000"? ... well in my example I display 54977 .. maybe I should have written 54977 instead of 5000.

    I hope you got the idea.

    Hi gorr-688214,

    In my example I used Rank() because the ContactId is not unique and I want to count the unique number of them.

    If you have no grouping clause then yes, you can use Row_number().

    Enjoy!

  • SELECTTOP 100 (select count(*) from X) AS Total_Counts,

    *

    FROMX

  • Hi DavidP-340734,

    Did you ever try your solution?

    I've tested on a table with more the 3 mil records and my solution is more faster than yours.

    Good luck!

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

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