sql server - Extracting ranked values from different rows - SQL -

i have database various categories. each category have 3 quantities, , want extract row containing 25th largest value each of quantities per category (ties can safely ignored).

for example, might have database rows towns or cities 1 of several countries. categories countries, , quantities might population, land area, , latitude. data like:

townname    country    population    landarea    latitude paris       france     500,715       47.9        45.76 manchester  usa        110,229       90.6        42.99 calais      france     72,589        33.5        50.95 leicester   england    337,653       73.3        52.63 dunkirk     france     90,995        43.9        51.04 ...         ...        ...           ...         ... 

in example, end result i'd want each of countries in list, along 25th largest population, 25th largest land area , 25th largest latitude. no longer resembles specific town or city, gives information each country. might like:

country    population    landarea    latitude france     144,548       83.95       50.21 poland     141,080       88.3        54.17 australia  68,572        146         -21.35 ...        ...           ...         ... 

i've figured out 1 way this, following:

1. use row_number function rank 1 of population, landarea , latitude in descending order, partitioned on countries.

2. repeat 3 times (one each quantity), , join 3 databases together. in on statement, ensure values of country columns equal, values of rank columns.

3. use where statement pull out row each country rank 25.

i don't method because involved creating 3 exact copies of decent sized chunks of code 3 separate databases joined (each of blocks of code in join statments decent size because simplified example, , had other stuff stage this).

i wondering whether there way wouldn't involve me repeating large chunks of code join statement makes code big , ugly. also, seems may crop time , time again, more efficient method wonderful.

thanks time

perhaps if can't find way eliminate 3-join approach, can simplify join condition assigning each distinct tuple groupid:

;with     mastercte     (         select      *,                     dense_rank() on (order country) groupid -- don't use row_number here. rank or demse_rank                mytable     ),     cte1     (         select      groupid, [population],                     row_number() on (partition groupid order [population] desc) populationrank                mastercte     ),     cte2     (         select      groupid, landarea,                     row_number() on (partition groupid order landarea desc) landarearank                mastercte     ),     cte3     (         select      groupid, latitude,                     row_number() on (partition groupid order latitude desc) latituderank                mastercte     )   select distinct  -- remember include distinct             mastercte.country,             cte1.population, cte2.landarea, cte3.latitude        mastercte    inner join  cte1        on mastercte.groupid = cte1.groupid , cte1.populationrank = 25 inner join  cte2        on mastercte.groupid = cte2.groupid , cte2.landarearank   = 25 inner join  cte3        on mastercte.groupid = cte3.groupid , cte3.latituderank   = 25