SQL command not properly ended with WHERE statement

Question

With this statement:

SELECT CITY, LENGTH(CITY) 
FROM STATION 
ORDER BY LENGTH(CITY) 
WHERE ROWNUM = 1;

why do I get this error:

ERROR at line 1:
ORA-00933: SQL command not properly ended SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) WHERE ROWNUM = 1

Answer 1

You probably want this:

select * from (
    SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY)
) WHERE rownum = 1;

You have a syntax error in your query, because the ORDER BY comes after the WHERE clause. So, you'll have this after fixing the syntax error:

SELECT CITY, LENGTH(CITY) 
FROM STATION
WHERE ROWNUM = 1
ORDER BY LENGTH(CITY)

If you recall, the WHERE clause is evaluated before the ORDER BY clause. Since, ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on. the ROWNUM return values 1,2,3,... based on the order in which data from table station is fetched (could be in any order), which is probably not what you want.

If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows.

So, you query becomes this:

select * from (
    SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY)
) WHERE rownum = 1;

In this, the rows are first sorted based on the length of content of CITY column inside the subquery, and then applies ROWNUM in the increasing order of the length of CITY column. So, now you can filter out the first row using ROWNUM = 1 predicate.

Alternatively, in Oracle database 12c and above, you can use the FETCH FIRST N ROWS ONLY clause to make your life easier (no subquery required).

SELECT CITY, LENGTH(CITY) 
FROM STATION
ORDER BY LENGTH(CITY)
FETCH FIRST 1 ROW ONLY;

This clause will be evaluated after the ordering is done and returns the top one row.