Results 1 to 1 of 1

Thread: COUNT(*) and Display TOP 100

Threaded View

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2008
    Posts
    9

    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
  •  



Click Here to Expand Forum to Full Width