SQL Server查询最长最短长度城市：去重及子查询排序问题求助

Hey there! Let's work through your two SQL Server issues step by step—they're both common pitfalls, so I’ll break down solutions that fit your needs.

Your current query returns all cities that match the maximum or minimum length, which is why you’re seeing multiple matching-length results. If you want to return only one representative per extreme (e.g., the alphabetically first city for each length), here are two solid approaches:

Option 1: Use Window Functions (Clean and Scalable) #

Window functions let you rank cities within their length groups, then pick only the top-ranked one for each extreme:

WITH CityRankings AS (
    SELECT 
        city,
        LEN(city) AS city_length,
        -- Rank cities in each length group alphabetically
        ROW_NUMBER() OVER (PARTITION BY LEN(city) ORDER BY city) AS rank_in_group
    FROM Station
),
LengthExtremes AS (
    SELECT 
        MAX(LEN(city)) AS max_length,
        MIN(LEN(city)) AS min_length
    FROM Station
)
SELECT cr.city, cr.city_length
FROM CityRankings cr
JOIN LengthExtremes le 
    ON cr.city_length IN (le.max_length, le.min_length)
WHERE cr.rank_in_group = 1 -- Only pick the first city in each length group
ORDER BY cr.city_length, cr.city;

Option 2: Use TOP 1 with UNION ALL (Simple and Direct) #

If you prefer a more concise approach, you can fetch the shortest and longest cities separately, then combine them:

SELECT city, LEN(city) AS city_length
FROM (
    -- Get alphabetically first shortest city
    SELECT TOP 1 city, LEN(city) FROM Station ORDER BY LEN(city), city
    UNION ALL
    -- Get alphabetically first longest city
    SELECT TOP 1 city, LEN(city) FROM Station ORDER BY LEN(city) DESC, city
) AS ExtremeCities
ORDER BY city_length, city;

If you actually want all cities at the extreme lengths but just want to avoid duplicate city names (if your table has duplicates), add DISTINCT to your original query:

SELECT DISTINCT city, LEN(city) AS l 
FROM Station 
WHERE LEN(city) IN (
    (SELECT MAX(LEN(city)) FROM Station) 
    UNION ALL 
    (SELECT MIN(LEN(city)) FROM Station)
) 
ORDER BY l, city;

When you try to add ORDER BY to the subqueries inside your IN clause, SQL Server throws an error because IN expects an unordered set of values. Sorting the subquery doesn’t change the values returned, so SQL sees it as unnecessary and blocks it.

If your goal is to sort the final results (which it looks like it is, since you have ORDER BY l,city in your original query), keep that outer sort—you don’t need sorting in the subqueries. If you want to control which city is picked for each extreme length, use the window function or TOP 1 approaches above, where the sorting logic is applied in a context that actually affects the results.

Also, a quick optimization: replace UNION with UNION ALL in your original subquery. UNION removes duplicates, but since max and min length can’t be the same (unless all cities have identical length), UNION ALL is faster and avoids unnecessary processing.

内容的提问来源于stack exchange，提问作者J.L