-
Nov 10th, 2009, 10:11 AM
#1
Thread Starter
New Member
COUNT(*) and Display TOP 100
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).
Let's say you have a table, ContactProperty where a Contact can have more properties:
CONTACTID PROPERTYID
1 10
1 10
2 20
So, the next script returns the top 100 UNIQUE contactidIds.
;with res (RowNo, contactid)
AS
(
SELECT
Rank() OVER (ORDER BY contactid) RowNo,
contactid
FROM
ContactProperty
GROUP BY
contactid
)
SELECT TOP 100 rowno, contactid
FROM res
ORDER BY Rowno DESC
This is what you get:
rowno contactid
54977 4378508
54976 4378507
54975 4378495
54974 4378421
54973 4378466
.........................
The value of RowNo from the first record represents the COUNT(*) of the whole result set, in this case I have 54977 rows but this returns only 100 of them.
This is a particular case but you can remove the "GROUP BY".
ENJOY!
Luigi
Last edited by halford13; Nov 11th, 2009 at 09:41 AM.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|