COUNT(*) and Display TOP 100

[halford13]

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