question

MohammedArshadAlikhan-9926 avatar image
0 Votes"
MohammedArshadAlikhan-9926 asked DanGuzman answered

Getting syntax error in sql

 create table station(
 city nvarchar(max))
 insert into station(city)
 values
 ('ABC'),('DEF'),('PQRS'),('WXY')
 ;with cte1 as(
 select city,LEN(city) as len1
 from station
 )
 select top 1 city from cte1 where len1=(select min(len1) from cte1) order by city
 select top 1 city from cte1 where len1=(select max(len1) from cte1) order by city 
azure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

DanGuzman avatar image
0 Votes"
DanGuzman answered

The reason for the syntax error is one can have only one query after CTE definitions.

As I understand it, you want one row with the city having the shortest city name followed by the row with the longest city name, using city name to break ties when multiple cities have the same length. One approach is with additional CTEs for the min/max lengths followed by a UNION ALL query:

 WITH cte1 AS (
         SELECT city , LEN(city) as len1
         FROM station
      )
     ,cte2 AS (
         SELECT TOP 1 city, len1
         FROM cte1
         ORDER by len1 ASC, city ASC
     )
     ,cte3 AS (
         SELECT TOP 1 city, len1
         FROM cte1
         ORDER by len1 DESC, City DESC
 )
 SELECT city
 FROM cte2
 UNION ALL
 SELECT city
 FROM cte3
 ORDER BY len1;

A less verbose method is with RANK() windowing functions to identify the min/max values:

 WITH cte1 as(
         SELECT
               city
             , LEN(city) as len1
             , RANK() OVER(ORDER BY LEN(city), city) AS MinCityRank
             , RANK() OVER(ORDER BY LEN(city) DESC, city) AS MaxCityRank
         FROM station
 )
 SELECT city 
 FROM cte1
 WHERE 1 IN(MinCityRank, MaxCityRank)
 ORDER by len1;



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.