Zeus Zeus - 7 months ago 12
SQL Question

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.

Answer

There's several things wrong here.

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

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.