Weather Observation Station 15

  • + 1 comment

    Using Oracle...
    Most of the answers listed in this discussion have answers like this:

    // Solution1 -- order by desc and select top
    select * from 
      (select round(LONG_W,4) from STATION
        where LAT_N<137.2345
        order by LAT_N desc) 
     where rownum=1;
    

    Is there anything worse about this pattern? I would have thought ordering was more resource intensive than assigning a variable and seeking. Is there a reason not to do this?

    // Solution 2 -- assign max_lat_n variable and seek
    variable max_lat_n number;
    exec select max(LAT_N) into :max_lat_n from STATION where LAT_N<137.2345;
    select round(LONG_W,4) from STATION where LAT_N=:max_lat_n;
    


    And does this subquery approach repeat the max() operation on every row?

    // Solution 3 -- subquery the max() operation
    select round(LONG_W,4) from STATION
      where LAT_N=(select max(LAT_N) from STATION
                     where LAT_N<137.2345);