I want to find the city and length of city (i.e. number of characters in the word CITY) where the length of city is maximum in the table. In case, we have more than one value we have to take only the first value on the basis of alphabetical arrangement of the city.
There is a weird thing I observe:
SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC, CITY;
gives me the correct table with the desired output as the first row with Length(City) = 21. But, when I try to retrieve the first row using
SELECT CITY, LENGTH(CITY) FROM STATION WHERE ROWNUM = 1 ORDER BY LENGTH(CITY) DESC, CITY;
query in Oracle it gives me some other row which has Length(City) = 12.
Please help me understand what is going wrong! Also FYI, there is just one value with maximum length of city.
The WHERE clause of your query is executed before ORDER BY so the ROWNUM = 1 is not the expected row. You can use FETCH FIRST ROW ONLY instead:
SELECT CITY, LENGTH(CITY)
FROM STATION
ORDER BY LENGTH(CITY) DESC, CITY
FETCH FIRST ROW ONLY;
... or you put the statement in a sub-select and use WHERE with ROWNUM on this:
SELECT *
FROM (
SELECT CITY, LENGTH(CITY)
FROM STATION
ORDER BY LENGTH(CITY) DESC, CITY
)x WHERE ROWNUM = 1;