2

I am not entirely sure how to word this in the correct way to properly express what it is I am looking for. But here it goes. I want to locate the three closest distinct units for each of my sites. I have my measured distances as total_mile, the starting locations are from_site_, and the end locations are to_unitcod. However, there are sometimes multiple end locations associated with the same to_unitcod that are within the top three shortest distances, where I need three different units for each site.

The code below is the closest I have gotten so far. What could I add to this that will make it select three different/distinct to_unitcod for each from_site_? Right now it is pulling the three shortest measured distances between each site and unit, which is good, but I would like it to pull the three shortest distances from three different units (to_unitcod).

    SELECT *
    FROM
        (
        SELECT nearrank, total_mile, from_site_, to_poiname, to_unitcod, to_unitnam,
        ROW_NUMBER() OVER (PARTITION BY from_site_ ORDER BY total_mile ASC) rn
        FROM public.mytable
        ) X
    WHERE rn<=3
    ORDER BY from_site_;

Below is a screen shot of the resulting table from the above code if that helps illustrate the problem at all. So for example, the records for the Yukon Weapons Range have one FAIR and two DENA unit codes - where that second DENA needs to be the next closest to_unitcod.

enter image description here

2
0

to_poiname is not unique so we need to remove that from the query.

Given that to_unitcod is what is being treated as distinct, we can run the following which will get the minimum distance (min_dist) for each from_site_ and to_unitcod pair.

This will return the from_site_, to_unitcod, min_dist, to_unitnam and rn:

SELECT *
FROM
    (
    SELECT from_site_, to_unitcod, MIN(total_mile) min_dist, to_unitnam,
    ROW_NUMBER() OVER (PARTITION BY from_site_ ORDER BY MIN(total_mile)) rn
    FROM public.mytable
    GROUP BY from_site_, to_unitcod,  to_unitnam
    ORDER BY from_site_, MIN(total_mile)
    ) X
WHERE rn<=3
ORDER BY from_site_;

The result will be: Simple Results


If we also do need to see the to_poiname and other fields we can do an outer selection on this table as follows:

SELECT a.*, X.rn
FROM
public.mytablea,
    (
    SELECT from_site_, to_unitcod, MIN(total_mile) min_dist, to_unitnam,
    ROW_NUMBER() OVER (PARTITION BY from_site_ ORDER BY MIN(total_mile)) rn
    FROM public.mytable
    GROUP BY from_site_, to_unitcod, to_unitnam
    ORDER BY from_site_, MIN(total_mile)
    ) X
WHERE rn<=3 AND (a.from_site_ = X.from_site_ AND a.to_unitcod = X.to_unitcod AND a.total_mile = X.min_dist)
ORDER BY from_site_;

The result of this will be: enter image description here

NOTE: I am using a subset of the data so some of the from_site_ locations only have 2 distinct nearest units, also I didn't populate my to_unitnam field

Not the answer you're looking for? Browse other questions tagged or ask your own question.