sql - recursive geometric query : five closest entities -

the question whether query described below can done without recourse procedural logic, is, can handled sql , cte , windowing function alone? i'm using sql server 2012 question not limited engine.

suppose have national database of music teachers 250,000 rows:

  teachername, address, city, state, zipcode, geolocation, primaryinstrument 

where geolocation column geography::point datatype optimally tesselated index.

user wants 5 closest guitar teachers location. query using windowing function performs enough if pick arbitrary distance cutoff, 50 miles, not selecting 250,000 rows , ranking them distance , taking closest 5.

but arbitrary 50-mile radius cutoff might not succeed in encompassing 5 teachers, if, example, user picks instrument different culture, such sitar or oud or balalaika; there might not 5 teachers of such instruments within 50 miles of location.

also, imagine have query conservatory of music has sent list of 250 singers, students have been accepted school upcoming year, , want send them 5 closest voice coaches each person on list, students can arrange coaching before arrive on campus. have scan teachers database 250 times (i.e. scan geolocation index) because students live @ different places around country.

so, wondering, possible, latter query involving list of 250 student locations, write recursive query radius begins small, @ 10 miles, say, , increases 10 miles each iteration, until either maximum radius of 100 miles has been reached or required 5 (5) teachers have been found? , can done students have yet matched required 5 teachers?

i'm thinking cannot done sql alone, , must done looping , temporary table--but maybe that's because haven't figured out how sql alone.

p.s. primaryinstrument column reduce size of set ranked distance sake of question forget that.

edit: here's example query. singer (submitted) dataset contains column arbitrary radius limit geo-results smaller subset, stated above, radius may define circle (whose centerpoint student's geolocation) might not encompass required number of teachers. supplied datasets contain thousands of addresses, not merely few hundred.

select teachersrankedbydistance.* ( select studentsandteachersinradius.*, rowpos = row_number()  over(partition          studentsandteachersinradius.zipcode+studentsandteachersinradius.streetaddress        order distanceinmiles) ( select singer.name,  singer.streetaddress,  singer.city,  singer.state,  singer.zipcode,  teachers.name teachername,  teachers.streetaddress teacheraddress,  teachers.city teachercity,  teachers.state teacherstate,  teachers.zipcode teacherzip, teachers.teacherid, geography::point(singer.lat, singer.lon, 4326).stdistance(teachers.geolocation)             / (1.6 * 1000) distanceinmiles  singer left join teachers on  ( teachers.geolocation).stdistance( geography::point(singer.lat, singer.lon, 4326))         < (singer.radius * (1.6 * 1000 ))  , teachers.primaryinstrument='voice' ) studentsandteachersinradius ) teachersrankedbydistance rowpos < 6    -- closest 5 abitrary requirement given 

i think may if need closest 5 teachers regardless of radius, write this. student duplicate 5 time in query, don't know want get.

select     s.name,      s.streetaddress,      s.city,      s.state,      s.zipcode,      t.name teachername,      t.streetaddress teacheraddress,      t.city teachercity,      t.state teacherstate,      t.zipcode teacherzip,     t.teacherid,     t.geolocation.stdistance(geography::point(s.lat, s.lon, 4326))     / (1.6 * 1000) distanceinmiles singer s     outer apply (         select top 5 tt.*         teachers tt         tt.primaryinstrument='voice'         order tt.geolocation.stdistance(geography::point(s.lat, s.lon, 4326)) asc     ) t