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

  • tuyendev + 2 comments

    If you want use rownum, you should rewrite code like this

    SELECT * FROM (SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY)) WHERE ROWNUM <= 1;
    • dhananjayt771 + 3 comments

      SET @V1 := (SELECT MIN(LENGTH(CITY)) FROM STATION); SET @V2 := (SELECT CITY FROM STATION WHERE LENGTH(CITY) = @V1 ORDER BY CITY LIMIT 1);

      SET @V3 := (SELECT MAX(LENGTH(CITY)) FROM STATION); SET @V4 := (SELECT CITY FROM STATION WHERE LENGTH(CITY) = @V3 ORDER BY CITY LIMIT 1);

      SELECT @V2,@V1; SELECT @V4,@V3;

      • samircity + 0 comments

        This is the best answer for MYSQL, thanks

      • samircity + 2 comments

        I customised it for MS SQL Server as below:

        DECLARE @V1 VARCHAR(MAX)

        DECLARE @V2 VARCHAR(MAX)

        DECLARE @V3 VARCHAR(MAX)

        DECLARE @V4 VARCHAR(MAX)

        SET @V1 = (SELECT MIN(LEN(CITY)) FROM STATION);

        SET @V2 = (SELECT TOP 1 CITY FROM STATION WHERE LEN(CITY) = @V1 ORDER BY CITY );

        SET @V3 = (SELECT MAX(LEN(CITY)) FROM STATION);

        SET @V4 = (SELECT TOP 1 CITY FROM STATION WHERE LEN(CITY) = @V3 ORDER BY CITY );

        SELECT @V2,@V1; SELECT @V4,@V3;

        • bharath_narsa + 1 comment

          select top 1 CITY,min(len(CITY)) len from STATION group by CITY order by len asc; select top 1 CITY,max(len(CITY)) len from STATION group by CITY order by len desc;

          • pradeepsripada + 0 comments

            Thank you

        • idusortus + 0 comments

          Just getting in to SQL Server and was poking around for a clear example of how to implement variables - thanks for this! Seeing this implementation helped me connect some dots.

      • lvrosa + 2 comments

        Without use of variables (MySQL):

        SELECT CITY,LENGTH(CITY) FROM STATION WHERE LENGTH(CITY) = (
    SELECT MIN(LENGTH(CITY)) FROM STATION
) ORDER BY CITY LIMIT 1;

SELECT CITY,LENGTH(CITY) FROM STATION WHERE LENGTH(CITY) = (
    SELECT MAX(LENGTH(CITY)) FROM STATION
) ORDER BY CITY LIMIT 1;
        • ssheu + 2 comments

          Hi, I'm wondering - why does WHERE LENGTH(CITY) = MAX(LENGTH(CITY)) not work? I get a "Invalid use of group function" error.

          • mchuong + 0 comments

            Double check that you're not using Oracle, but that you're using MySQL

          • aashiq_baig + 0 comments

            That's because you need to use 'having' when you use a group function like max or length

        • claritywhisk + 1 comment

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

          • ashishsingh10001 + 0 comments

            what the is use of writing city before limit???

    • Ruch_ka + 1 comment

      It works, but also add city to order by list

      select * from (select city, length(city) from station order by length(city), city) where rownum <= 1;

select * from (select city, length(city) from station order by length(city) desc, city) where rownum <= 1;
      • johnasaville + 0 comments

        Simple and elegant. Nice.