MySQL String Exercises: Get the locations that have minimum street length

MySQL String: Exercise-11 with Solution

Write a MySQL query to get the locations that have minimum street length.

location_id  street_address        postal_code  city        state_province  country_id
-----------  --------------------  -----------  ----------  --------------  ----------
1000         1297 Via Cola di Rie  989          Roma                        IT
1100         93091 Calle della Te  10934        Venice                      IT
1200         2017 Shinjuku-ku      1689         Tokyo       Tokyo Prefectu  JP
1300         9450 Kamiya-cho       6823         Hiroshima                   JP
1400         2014 Jabberwocky Rd   26192        Southlake   Texas           US
1500         2011 Interiors Blvd   99236        South San   California      US
1600         2007 Zagora St        50090        South Brun  New Jersey      US
1700         2004 Charade Rd       98199        Seattle     Washington      US
1800         147 Spadina Ave       M5V 2L7      Toronto     Ontario         CA
1900         6092 Boxwood St       YSW 9T2      Whitehorse  Yukon           CA
2000         40-5-12 Laogianggen   190518       Beijing                     CN
2100         1298 Vileparle (E)    490231       Bombay      Maharashtra     IN
2200         12-98 Victoria Stree  2901         Sydney      New South Wale  AU
2300         198 Clementi North    540198       Singapore                   SG
2400         8204 Arthur St                     London                      UK
2500         Magdalen Centre, The  OX9 9ZB      Oxford      Oxford          UK
2600         9702 Chester Road     9629850293   Stretford   Manchester      UK
2700         Schwanthalerstr. 703  80925        Munich      Bavaria         DE
2800         Rua Frei Caneca 1360  01307-002    Sao Paulo   Sao Paulo       BR
2900         20 Rue des Corps-Sai  1730         Geneva      Geneve          CH
3000         Murtenstrasse 921     3095         Bern        BE              CH
3100         Pieter Breughelstraa  3029SK       Utrecht     Utrecht         NL
3200         Mariano Escobedo 999  11932        Mexico Cit  Distrito Feder  MX

Code:

-- This SQL query selects all columns from the locations table where the length of the street address is less than or equal to the minimum length of all street addresses in the locations table.

SELECT 
    * -- Selecting all columns from the locations table.

FROM 
locations -- Specifies the table from which data is being retrieved, in this case, it's the 'locations' table.

WHERE 
LENGTH(street_address) <= ( -- Filters the rows where the length of the street address is less than or equal to...

        SELECT  
MIN(LENGTH(street_address)) -- ... the minimum length of all street addresses in the locations table.

        FROM 
locations
    );

Explanation:

• The outer SELECT statement retrieves all columns from the locations table.
• The WHERE clause filters the rows based on a condition.
• The condition compares the length of the street_address column in each row with the minimum length of all street addresses in the locations table.
• The inner SELECT statement calculates the minimum length of the street_address column in the locations table using the MIN() and LENGTH() functions.
• Rows where the length of the street address is less than or equal to the minimum length are selected for output.

Sample Output:

LOCATION_ID	STREET_ADDRESS	POSTAL_CODE	CITY			STATE_PROVINCE	COUNTRY_ID
1600		2007 Zagora St	50090		South Brunswick		New Jersey	US
2400		8204 Arthur St			London					UK

MySQL Code Editor:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous:Write a MySQL query to get the last word of the street address.
Next:Write a MySQL query to display the first word from those job titles which contains more than one words.