I am trying 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 fall 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 *
        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_;
  • Please provide create and insert statements (a fiddle would also help) to reproduce your problem. Jun 23 '21 at 16:39
  • Hi, and welcome to dba.se! Please don't upload images here for the reasons outlined in this link. Plese go to dbfiddle.uk and construct a fiddle with your tables, some sample data and a desired result... Help us to help you! Jun 23 '21 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.