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.
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')