Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Display COUNT(*) but ONLY TOP 100 Expand / Collapse
Author
Message
Posted Wednesday, November 11, 2009 3:51 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 4:06 PM
Points: 129, Visits: 141
Comments posted to this topic are about the item Display COUNT(*) but ONLY TOP 100
Post #817067
Posted Friday, November 20, 2009 6:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 20, 2009 6:49 PM
Points: 3, Visits: 2
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.
Post #822344
Posted Friday, November 20, 2009 7:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 22, 2014 7:22 AM
Points: 3, Visits: 38
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.
Post #822359
Posted Friday, November 20, 2009 8:25 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 4:06 PM
Points: 129, Visits: 141
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!
Post #822443
Posted Friday, November 20, 2009 4:40 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 1, 2014 10:08 AM
Points: 166, Visits: 103
SELECT TOP 100 (select count(*) from X) AS Total_Counts,
*
FROM X
Post #822787
Posted Monday, November 23, 2009 9:53 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 4:06 PM
Points: 129, Visits: 141
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!
Post #823355
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse