Oracle Analytic Question

This is what I came up with :

SELECT zr, min_distance  FROM (SELECT zr, min_distance, cnt,                row_number() over(PARTITION BY zr ORDER BY min_distance) rnk           FROM (SELECT zr.zip zr, zipdistance(zr.zip, za.zip) min_distance,                         COUNT(za.locations) over(                             PARTITION BY zr.zip                              ORDER BY zipdistance(zr.zip, za.zip)                         ) cnt                    FROM zips_required zr                   CROSS JOIN zips_available za)          WHERE cnt >= :N) WHERE rnk = 1

1. For each zip_required calculate the distance to the zip_available and sort them by distance
2. For each zip_required the count with range allows you to know how many zip_availables are in the radius of that distance.
3. filter (first where COUNT(locations) > N)

I used to create sample data:

INSERT INTO zips_required   SELECT to_char(10000 + 100 * ROWNUM) FROM dual CONNECT BY LEVEL <= 5;INSERT INTO zips_available   (SELECT to_number(zip) + 10 * r, 100 - 10 * r FROM zips_required, (SELECT ROWNUM r FROM dual CONNECT BY LEVEL <= 9));CREATE OR REPLACE FUNCTION zipdistance(zipfrom VARCHAR2,zipto VARCHAR2) RETURN NUMBER ISBEGIN   RETURN abs(to_number(zipfrom) - to_number(zipto));END zipdistance;/

Note: you used COUNT(locations) and SUM(locations) in your question, I assumed it was COUNT(locations)

SELECT  *FROM    (        SELECT  zip, zd, ROW_NUMBER() OVER (PARTITION BY zip ORDER BY rn DESC) AS rn2        FROM    (                SELECT  zip, zd, ROW_NUMBER() OVER (PARTITION BY zip ORDER BY zd DESC) AS rn                FROM    (                        SELECT  zr.zip, zipdistance(zr.zip, za.zip) AS zd                        FROM    zips_required zr                        JOIN    zips_available za                        )                )        WHERE   rn <= n        )WHERE   rn2 = 1

For each zip_required, this will select the minimal distance into which fit N zip_available's, or maximal distance if the number of zip_available's is less than N.

I solved the same problem by creating a subset of ZIP's within a square radius from the given zip (easy math: < or > NSWE radius ), then iterating through each entry in the subset to see if it was within the needed radius. Worked like a charm and was very fast.