SQL select city name and length with min & max lengths-CodePudding

Here is my SQL Script to get the 2 records only having Min. & Max. length of city

SELECT  CITY, LEN(CITY) [Length] 
FROM Addresses 
GROUP BY CITY
HAVING LEN(CITY) IN (MIN(LEN(CITY)), MAX(LEN(CITY)) )

Why it is returning all the records?

| CITY           |  Length  
-----------------------------
| Delhi          | 5        
| Gautham Nagar  | 13
| Mau            | 3
| New Delhi      | 9

It should return only two records with

Gautham Nagar  | 13
Mau            | 3

CodePudding user response：

This query should do it too:

SELECT      TOP (1)
            CITY
            , MIN ( LEN ( CITY ) )
                OVER ( ORDER BY LEN ( CITY ) ASC ) AS [Length]
FROM        Addresses
UNION
SELECT      TOP (1)
            CITY
            , MAX ( LEN ( CITY ) )
                OVER ( ORDER BY LEN ( CITY ) DESC ) AS [Length]
FROM        Addresses
ORDER BY    [Length] DESC ;

CodePudding user response：

This is the working solution replied by lptr in a comment

SELECT CITY, LEN(CITY) 
FROM (
    SELECT  
        CITY
      , MIN(LEN(CITY)) OVER() AS MINLength
      , MAX(LEN(CITY)) OVER() AS MAXLength FROM Addresses
    ) AS A 
WHERE LEN(CITY) IN (MINLength, MAXLength)