ORA-00907缺失右括号：修正Oracle SQL查询以按字符长度返回最小城市名

First, let's break down why your original query is throwing the ORA-00907: missing right parenthesis error:

• Oracle does not allow using AS when assigning an alias to a subquery. So (...) as a should be written as (...) a instead.
• Even if you fix that syntax error, your query's logic won't work as intended: the WHERE clause will return every row, since every city's length exists in the distinct list of lengths. It won't filter down to just the smallest city per length.

Solution 1: Simple GROUP BY Approach (Most Efficient) #

This is the cleanest way to get exactly what you need—group cities by their character length, then pick the smallest city name (Oracle uses lexicographical order for string comparisons, so Abc will be smaller than Def):

SELECT MIN(city) AS min_city, LENGTH(city) AS char_length
FROM station
GROUP BY LENGTH(city)
ORDER BY char_length;

Solution 2: Window Function (For More Flexibility) #

If you need to include additional columns later, using a window function like ROW_NUMBER() aligns closer to your original sorting-based approach:

SELECT city, leng
FROM (
    SELECT 
        city,
        LENGTH(city) AS leng,
        -- Assign row number within each length group, ordered by city name
        ROW_NUMBER() OVER (PARTITION BY LENGTH(city) ORDER BY city) AS rn
    FROM station
)
-- Only keep the first row (smallest city) per length group
WHERE rn = 1
ORDER BY leng;

Why Your Original Query Failed #

Beyond the syntax error with AS a, the WHERE a.leng IN (...) condition doesn't filter rows to the minimum city per length—it just keeps every row whose length exists in the table. The ordering in your inner subquery doesn't help because you never limit results to the first entry per length group.

内容的提问来源于stack exchange，提问作者Rahul Kapoor