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

  • marinskiy + 3 comments

    Here is Oracle solution from my HackerrankPractice repository:

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

    Feel free to ask if you have any questions :)

    • shinjinidatta25 + 0 comments

      Hey thanks for this.

    • csugirtha + 1 comment

      Many thanks, but why is ROWNUM=1 outside the select statements? Its not clear.

      • haastyle + 0 comments

        It is outside as you want to return just rownum = 1 from the inner query. The inner query is getting all the city and lengths, then orders it, the outside query is selecting all and then grabbing rownum = 1 from the inner query. If you do rownum = 1 in the inner query it will return the first row before it does the order by clause so you will not get the desired results.

        https://www.periscopedata.com/blog/sql-query-order-of-operations for more details

    • ashita_gaur + 0 comments

      Why ROWNUM =1 is used here ?