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

  • [deleted] + 3 comments

    Select City, Length(City) From Station Order by Length(City), City Limit 1;

    Select City, Length(City) From Station Order by Length(City) DESC, City Limit 1;

    • Naldershof + 1 comment

      Annoyingly enough the "equivalent"

      SELECT CITY, CHAR_LENGTH(CITY) FROM STATION ORDER BY 2 LIMIT 1;
SELECT CITY, CHAR_LENGTH(CITY) FROM STATION ORDER BY 2 DESC LIMIT 1;

      Doesn't work correctly since there's multiple entries having the same value for the shortest string. In a production setting you'd probably want to extend this answer to include a more complex order by.

      • lorbicki + 2 comments
        SELECT CITY, CHAR_LENGTH(CITY) FROM STATION ORDER BY 2, 1 ASC LIMIT 1;
SELECT CITY, CHAR_LENGTH(CITY) FROM STATION ORDER BY 2 DESC LIMIT 1;
        • rajapriyansarav1 + 1 comment

          Can i know why order by 2,1 is given

          • lorbicki + 1 comment

            Yes, when you type: 

            SELECT CITY, CHAR_LENGH(CITY) FROM STATION

            You mentioned two columns: CITY (1) and CHAR_LENGTH (2)

            So, when you type:

            SELECT CITY, CHAR_LENGTH(CITY) FROM STATION ORDER BY 2, 1

            You're saying: order first by CHAR_LENGTH (2) and after that take this results and order by CITY (1)

            • schachnerje + 1 comment

              Thanks for the answer. Why is it that when you type ORDER BY in:

              SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY 2, 1 DESC LIMIT 1;

              it does not work the same way as the ascending list? (It suddenly disregards sorting alphabetically)

              • andy_slowmo + 1 comment

                Because 'ORDER BY' function now has two parameters which are:

                • '2' - And will first order the results by this one
                • '1 DESC' - So if there are results with equal 'LENGTH(city)' after the first ordering it will order these (the equal length ones) by REVERSE alphabetical order.

                *Note: The DESC only applies to the 1 argument/parameter in front of it NOT to all of them;

                Further note: I am not a pro with MySQL but I've tested this a bit and this is what I noticed;

                • charlesgoh + 1 comment

                  @andy_slowmo Looks like you are right. So it seems if we wanted to be very detailed we could do: 

                  SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY 2 ASC, 1 ASC LIMIT 1;
SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY 2 DESC, 1 ASC LIMIT 1

                  That way the code looks more representative of the results

                  • xiaoxia_pan + 0 comments

                    Can I add a 'UNION' on your code, but it not working...just wondering

        • bishtkrishna7094 + 0 comments

          for largest city why not this? select city, length(city) from station order by 2 desc,1 limit 1;

    • karthikapanneer1 + 0 comments
      [deleted]
    • kev_rocky + 0 comments

      This is the cleanest query for this problem. Short and to the point!