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.”