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

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

        • Logan_47 + 0 comments

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