Nov 10th, 2009, 09:11 AM
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:
So, the next script returns the top 100 UNIQUE contactidIds.
;with res (RowNo, contactid)
Rank() OVER (ORDER BY contactid) RowNo,
SELECT TOP 100 rowno, contactid
ORDER BY Rowno DESC
This is what you get:
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".
Last edited by halford13; Nov 11th, 2009 at 08:41 AM.
Tags for this Thread
Click Here to Expand Forum to Full Width
Survey posted by VBForums.