Sub querying data over a SQL query

I've the following query, Query the Western Longitude (LONG_W) for the largest Northern Latitude (LAT_N) in STATION that is less than 137.2345 137.2345. Round your answer to 4 decimal places. The schema for the table STATION is id, city, state, lat_N, long_w

This is my code,

SELECT LONG_W 
FROM STATION 
WHERE LAT_N IN
(SELECT ROUND(MAX(LAT_N), 4) FROM STATION WHERE LAT_N < 137.2345);

This doesn't work, what am I doing wrong here.

Solution

There's several things wrong here.

For one thing, there is no guarantee that value in LAT_N is going to be equal to ROUND(LAT_N,4).

 78.123456 = ROUND(78.123456,4)   --> FALSE

I don't see any need for a subquery.

Maximum value for a latitude (in degrees) is going to be 90. I think the question was asking for values of LONG_W that were less than 137.2345

I read the question as specifying...

access the rows in the station table
exclude rows that don't satisfy LONG_W < 137.2345
from those rows, find the "largest" value LAT_N
round that largest value of LAT_N to 4 decimal places
return the rounded value

I'd write the query like this:

 SELECT ROUND(MAX(s.lat_n),4) AS largest_lat_n
    FROM station s
   WHERE s.long_w < 137.2345

Maybe I'm reading the question wrong. (This isn't a SQL problem, it's a specification problem.)

If the question is instead asking...

access the rows in the station table
exclude rows that don't satisfy LONG_W < 137.2345
from those rows, find the row with the "largest" value LAT_N
take the LONG_W from that row with largest LAT_N
round that LONG_W to 4 decimal places
return the rounded value

To get other values on the row that have the largest LAT_N, I could do something like this:

  SELECT ROUND(s.long_w,4)
    FROM station s
   WHERE s.long_w < 137.2345
   ORDER BY s.lat_n DESC
   LIMIT 1

If I want to use a subquery, there's a potential that we'll get multiple rows back with the same LAT_N, and some of those values of LONG_W could be larger than 137.2345

Consider a table containing rows with values:

LAT_N     LONG_W
-------   ---------
87.654321 131.313131
87.654321 140.404040

... and assume that the value of 87.654321 is the "largest" that occurs for rows with LONG_W < 137.2345. (That is, no other rows with a larger LAT_N.)

Consider the return from this statement:

 SELECT t.long_w
   FROM ( SELECT MAX(s.lat_n) AS largest_lat_n
            FROM station s
           WHERE s.long_w < 137.2345
        ) s
   JOIN station t
     ON t.lat_n = s.largest_lat_n

That's going to return both of the rows. If we do a MAX() function, we're going to get back the higher value, 140.4040404 value. And that doesn't seem to match the specification. To prevent larger values of LONG_W from being returned, we'd either need to add a predicate to the query (condition in the WHERE clause)

  WHERE t.long_w < 137.2345

Or, we could use an expression in the SELECT list to filter out LONG_W values that don't meet the specification

 SELECT MAX( IF(t.long_w<137.2345,t.long_w,NULL) ) AS long_w