Select the states and cities, then use Cross Apply (or Outer Apply) to get the top 30 percent for each.
Is that what you're trying to do?
Something like this:
select state, city, location
from dbo.MyCitiesTable
cross apply
(select top 30 percent location
from dbo.MyLocationsTable
where MyLocationsTable.CityID = MyCitiesTable.ID
order by location) as Locations ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon