# Shortest and longest city name and alphabetical order

Problem

Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

Sample Input

Let's say that CITY only has four entries: DEF, ABC, PQRS and WXY

Sample Output

ABC 3
PQRS 4

Can I get shorter code in MySQL and also an optimal Oracle query? There's no way to have only one sub-query for both min and max, is there?

select city, char_length(city) from STATION
where city = (select min(city) from STATION
where char_length(city) = (select min(char_length(city)) from STATION))

or city = (select min(city) from STATION
where char_length(city) = (select max(char_length(city)) from STATION));

• I'm not up for writing a review, but you should look into TOP/LIMIT and use ORDER BY to make the query more clear Nov 5, 2017 at 10:09
• Is there any particular reason you are requesting "also an optimal Oracle query"? Oracle is a completely separate database management system (arguably a better one) than MySQL. Nov 5, 2017 at 11:18
• SELECT city, char_length(city) FROM station ORDER BY char_length(city) DESC, city LIMIT 1 might do the trick (but test). If you're golfing, ORDER BY 2 DESC, 1 might work for the ORDER BY clause.
– user1149
Nov 5, 2017 at 13:08
• @Phrancis I have learnt Oracle only. Just for this problem, I did a MySQL query - but it's not what I generally use. That is why I'm looking for an Oracle implementation. Nov 6, 2017 at 2:41
• @BarryCarter What does ORDER BY 2 mean? Nov 6, 2017 at 2:41

## Formatting

Your SQL statement formatting is not very good. I would encourage you to use a free tool like sql-format.com (or one of the many others) to format your SQL queries in a more readable way. This is your original query, with better formatting (I indented the subqueries by hand because the tool didn't).

SELECT
city,
CHAR_LENGTH(city)
FROM STATION
WHERE city = (
SELECT
MIN(city)
FROM STATION
WHERE CHAR_LENGTH(city) = (
SELECT
MIN(CHAR_LENGTH(city))
FROM STATION
)
)
OR city = (
SELECT
MIN(city)
FROM STATION
WHERE CHAR_LENGTH(city) = (
SELECT
MAX(CHAR_LENGTH(city))
FROM STATION
)
);


Now we can see much more easily how deeply nested queries are.

## Variables

You could make the code simpler by using a few user-defined variables:

SET @MinCityLen = (SELECT MIN(CHAR_LENGTH(city)) FROM STATION);
SET @MaxCityLen = (SELECT MAX(CHAR_LENGTH(city)) FROM STATION);
/* Query below only to demonstrate the variables */
SELECT
'@MinCityLen' AS VariableName,
@MinCityLen AS Value
UNION
SELECT
'@MaxCityLen',
@MaxCityLen;


Which returns this:

VariableName  Value
@MinCityLen   3
@MaxCityLen   4


This will abstract away one level of nesting and make the query simpler to understand. Note that the @ symbol is just a convention, it is not needed as far as MySQL syntax goes. Some database systems do require the @ symbol though, the most famous being Microsoft SQL Server.

## Shorter code != Better code

Sometimes, better code is longer rather than shorter. This is often the case with SQL. Longer code can be better formatted, better documented, better abstracted, better structured. In my personal SQL experience, short queries, unless they are very simple, are often pretty bad. Yours is not a case of a very simple query.

This is what I came up with. I also have a link on sqlfiddle

# find shortest city name
SET @MinCityLen = (SELECT MIN(CHAR_LENGTH(city)) FROM STATION);
# find longest city name
SET @MaxCityLen = (SELECT MAX(CHAR_LENGTH(city)) FROM STATION);

SELECT
city,
CHAR_LENGTH(city)
FROM
STATION
WHERE
# find shortest city name sorted alphabetically
city = (
SELECT
city
FROM STATION
WHERE CHAR_LENGTH(city) = @MinCityLen
ORDER BY city ASC
LIMIT 1
)
# find longest city name sorted alphabetically
OR city = (
SELECT
MIN(city)
FROM STATION
WHERE CHAR_LENGTH(city) = @MaxCityLen
ORDER BY city ASC
LIMIT 1
);

• Thank you for your formatting help! The shorter MySQL code returns "@MinCityLen", but I want the city name to be displayed. Also, thank you for the last documented code. Is there any way to merge both max and min finding queries into one? Nov 6, 2017 at 2:47

It took me a while to think about the answer and how to present it in a presentable manner. The following works just fine:

SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY), CITY ASC LIMIT 1; SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC LIMIT 1;

• You have presented a good alternative solution (exactly how I would approach the problem), but you haven't reviewed the code. Please edit it to explain your reasoning (how your solution works and why that is an improvement upon the original) so that everyone can learn from your thought process. Jun 26, 2018 at 10:49

I think, presentation matters. My solution shows each parts separately in a human readable manner. The steps show how to break the problem before we begin to solve it

Idea : Find all cities that have the smallest length.

STEPS:

• Find the minimum length of city .
• Get all cities that have the MIN Length.
• Sort by CITY .
• Output the first result only.

Repeat same for MAX

select CITY, length(CITY) from STATION where length(CITY) = (
select MIN(length(CITY)) from STATION
) order by CITY limit 1;

select CITY, length(CITY) from STATION where length(CITY) = (
select MAX(length(CITY)) from STATION
) order by CITY limit 1;

• You have presented an alternative solution, but haven't reviewed the code. Please edit it to explain your reasoning (how your solution works and how it improves upon the original) so that everyone can learn from your thought process. Jun 26, 2018 at 8:15