6
\$\begingroup\$

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));
\$\endgroup\$
  • 2
    \$\begingroup\$ 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 \$\endgroup\$ – Vogel612 Nov 5 '17 at 10:09
  • \$\begingroup\$ 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. \$\endgroup\$ – Phrancis Nov 5 '17 at 11:18
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – Barry Carter Nov 5 '17 at 13:08
  • \$\begingroup\$ @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. \$\endgroup\$ – ProgramSpree Nov 6 '17 at 2:41
  • \$\begingroup\$ @BarryCarter What does ORDER BY 2 mean? \$\endgroup\$ – ProgramSpree Nov 6 '17 at 2:41
6
\$\begingroup\$

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
    );
\$\endgroup\$
  • \$\begingroup\$ 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? \$\endgroup\$ – ProgramSpree Nov 6 '17 at 2:47
4
\$\begingroup\$

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;

\$\endgroup\$
  • \$\begingroup\$ 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. \$\endgroup\$ – Toby Speight Jun 26 '18 at 10:49
1
\$\begingroup\$

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;
\$\endgroup\$
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – Toby Speight Jun 26 '18 at 8:15

protected by Community Dec 24 '18 at 2:39

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.