You are viewing a single comment's thread. Return to all comments →
Oracle provides the FETCH FIRST mechanism which allows you to select n rows with order by. This is much better then SELECT * around your whole statement.
SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY),CITY FETCH FIRST 1 ROWS ONLY;
Note: The Orderby is executed before we fetch the first row.
Yes, this came with Oracle Database 12c (https://docs.oracle.com/database/121/LNPLS/release_changes.htm#LNPLS113).
SELECT * FROM V$VERSION;
Oracle Database 11g Express Edition Release 184.108.40.206.0 - 64bit Production
PL/SQL Release 220.127.116.11.0 - Production
CORE 18.104.22.168.0 Production
TNS for Linux: Version 22.214.171.124.0 - Production
NLSRTL Version 126.96.36.199.0 - Production
I get an error when trying this:
SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY), CITY FETCH FIRST 1 ROWS ONLY
ERROR at line 1:
ORA-00933: SQL command not properly ended
this is not a right query or syntex. please do read about syntex of "ORDER BY" and also on "HAVING" clause.
the syntax for ORDER BY is correct. If I run the exact same query without the FETCH clause then the query works just fine.
Going off these docs, my query with FETCH should work just fine. https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1
Try this in "oracle" envoirment:
select city,length_city from (select a.*, rownum r from (select length(city) length_city,city from station order by length_city, city) a) where r in (1,(select count(*) from station))
Yes I'm using Oracle DB, as my original post stated.
Thank you for your answer, but I am wondering why Oracle's FETCH clause is not working here...
Can you explain this query?
if there are more cities with length 21 this doesnt work
Semicolon is missing
can you explain this code