Display COUNT(*) but ONLY TOP 100

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Luigi Marinescu » Display COUNT(*) but ONLY TOP 100

Display COUNT(*) but ONLY TOP 100

Rate Topic

Display Mode

Topic Options

Author

Message

halford13

Posted Wednesday, November 11, 2009 3:51 AM

SSC-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

Mike1024

Posted Friday, November 20, 2009 6:50 AM

Forum 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

gorr-688214

Posted Friday, November 20, 2009 7:03 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 15, 2013 6:57 AM
Points: 2, Visits: 25

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

halford13

Posted Friday, November 20, 2009 8:25 AM

SSC-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

DavidP-340734

Posted Friday, November 20, 2009 4:40 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 05, 2013 12:50 PM
Points: 165, Visits: 83

SELECT TOP 100 (select count(*) from X) AS Total_Counts,
*
FROM X

Post #822787

halford13

Posted Monday, November 23, 2009 9:53 AM

SSC-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 »

Permissions