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

  • 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;