Zeus - 1 year ago 66

SQL Question

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.

Source (Stackoverflow)