We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
Loading...
  1. Practice
  2. SQL
  3. Basic Select
  4. Weather Observation Station 5
  5. Discussions

Weather Observation Station 5

  • sebi_essling + 4 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.

    For example:

    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.

    • wittrup + 0 comments

      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 11.2.0.2.0 - 64bit Production 
PL/SQL Release 11.2.0.2.0 - Production 
CORE 11.2.0.2.0 Production 
TNS for Linux: Version 11.2.0.2.0 - Production 
NLSRTL Version 11.2.0.2.0 - Production
    • adam_allard + 2 comments

      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
      • virenderkumar + 1 comment

        this is not a right query or syntex. please do read about syntex of "ORDER BY" and also on "HAVING" clause.

        • adam_allard + 1 comment

          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

          • virenderkumar + 3 comments

            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))

            • adam_allard + 0 comments

              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...

            • faiza_fiz2015 + 0 comments

              Can you explain this query?

            • aravind_bijjala1 + 0 comments

              if there are more cities with length 21 this doesnt work

      • srishti_me + 0 comments

        Semicolon is missing

    • kumarikajaldell + 0 comments

      can you explain this code

    • arpitmalviya5nov + 0 comments

      NOT WORKING