sqlsql-server-2008subqueryunion

Query to find the city name with longest and shortest length

I've written a query to return me the cities with the shortest and longest length strings in a MS SQL SERVER database.

Select city, len(city) as l 
From Station Where len(city) in 
((select max(len(city)) from station)
Union
(select min(len(city)) from station)) 
Order by l,city;

My difficulty is that I get duplicates, because I have a few cities that have the longest and shortest length. Also when I try to ORDER BY CITY in both sub queries it fails.

Any advice?

Solution

  • Another way:

    select * from (
         select top 1 city, LEN(city) cityLength from station order by cityLength ASC,city ASC) Minimum
       UNION
       select * from (
       select top 1 city, LEN(city) cityLength from station order by cityLength desc, city ASC) Maximum