Search code examples
sqlaggregation

why ALL(SELECT) doesn't work to get max value


I'm working on Weather Observation Station 15 from Hackerrank (Hackerrank Question)

It asked

Query the Western Longitude (LONG_W) for the largest Northern Latitude (LAT_N) in STATION that is less than 137.2345 . Round your answer to decimal places.

STATION Table got 5 columns: ID, CITY, STATE, LAT_N, LONG_W.

Below is the my query and I'm trying to figure out why it does not work.

SELECT ROUND(LONG_W, 4) FROM STATION
WHERE LAT_N >= ALL(SELECT LAT_N FROM STATION WHERE LAT_N < '137.2345')

When I tried a different query and this works

SELECT ROUND(LONG_W, 4) FROM STATION
WHERE LAT_N = (SELECT MAX(LAT_N) FROM STATION WHERE LAT_N < '137.2345')

What's wrong with the first query? Thanks.


Solution

  • This query:

    SELECT ROUND(LONG_W, 4) FROM STATION
    WHERE LAT_N >= ALL(SELECT LAT_N FROM STATION WHERE LAT_N < '137.2345')
    

    will return all the rows where LAT_N is greater or equal to '137.2345' because it is missing an additional condition.

    It should have been written as:

    SELECT ROUND(LONG_W, 4) FROM STATION
    WHERE LAT_N <  '137.2345'
      AND LAT_N >= ALL(SELECT LAT_N FROM STATION WHERE LAT_N < '137.2345')