• Thanks for the reply.

    By chance, I came across a very good blog about the same which uses the following CTE :-

    (http://weblogs.sqlteam.com/jeffs/archive/2008/02/21/Top-N-Percent-per-Group.aspx)

    with AttributebyRegion as

    (

    select addressid,statecode, cityname,

    rank() over (partition by cityname,statecode order by addressid desc) as AddedRank,

    count(*) over (partition by cityname,statecode) as RegionCount

    from lOCATTRIBUTE_Flagid

    )

    select

    distinct statecode, COUNT_State = FLOOR (COUNT(RegionCount * .30))

    from

    AttributebyRegion

    where

    AddedRank <= (RegionCount * .30)

    group by statecode

    order by statecode

    -----

    ______________________________________________________________________

    Ankit
    MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
    "Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
    You are already naked. There is no reason not to follow your heart.”