CODEWITHSUNDEEP
sqlaggregation
hachiko
hachiko

Reputation: 55

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.

Upvotes: 1

Views: 612

Answers (2)

Mohamed
Mohamed

Reputation: 43

This query :

select round (long_w,4)
from station
where lat_n<137.2345
order by lat_n desc
limit 1;

firstly, it is selecting all rows with lat_n less than 137.2345.

secondly, it orders the result by lat_n in descending order so the first row has the largest lat_n that is less than 137.2345.

Thirdly, I set a limit equal to 1 to only get the first row, then I get the round of long_w using the round function.

Syntax
ROUND(number, decimals, operation)

Upvotes: 0

forpas
forpas

Reputation: 164174

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

Upvotes: 1

Related Questions