Shortest mysql length

Question

I don’t know how to explain what I want, then to give an example

country                           prefix
Argentina-Mobile                  549
Argentina-Neuquen                 54299
Argentina-Rosario                 54341
Argentina-Salta                   54387
Argentina-Santa Fe                54342
Argentina-Tucuman                 54381
Armenia                           374
Armenia Mobile-K-Telecom          37477
Armenia Mobile-K-Telecom          37493
Armenia Mobile-K-Telecom          37494
Armenia Mobile-K-Telecom          37498
Armenia-Karabakh                  37447
Armenia-Mobile                    37455
Armenia-Mobile                    3749
Armenia-Yerevan                   37410
Aruba                             297
Aruba-Mobile                      29756
Aruba-Mobile                      29759
Aruba-Mobile                      29766
Aruba-Mobile                      29769
Aruba-Mobile                      29796
Aruba-Mobile                      29799
Aruba-Mobile-Digicell             29773
Aruba-Mobile-Digicell             29774
Aruba-Mobile-MIO                  297600
Aruba-Mobile-MIO                  297622
Ascension Island                  247
Australia                         61
Australia-Adelaide/Perth          61861
Australia-Adelaide/Perth          61862
Australia-Adelaide/Perth          61863

I want to run a query in a prefix to get a list of the shortest parent prefix

country                  prefix
Argentina -Mobile        549
Armenia                  374
Aruba                    297
Australia                61

Answer 1

This is easier than I thought. You just need to group by country, then use MIN().

This would be much simpler, although less error prone, if you had a country code column for each field, instead of analyzing the country text, which could lead to errors.

SELECT t2.country, MIN(CAST(t1.prefix AS SIGNED)) AS prefix FROM MyTable t1
LEFT JOIN MyTable t2
ON t2.prefix = t1.prefix
GROUP BY
  IF(
     INSTR(t1.country, ' mobile') = 0 AND INSTR(t1.country, '-') = 0,
     t1.country,
     IF(
        INSTR(t1.country, ' mobile') > 0 AND INSTR(t1.country, '-') > 0,
        IF(
           INSTR(t1.country, ' mobile') > INSTR(t1.country, '-'),
           LEFT(t1.country, INSTR(t1.country, '-') - 1),
           LEFT(t1.country, INSTR(t1.country, ' mobile') - 1)
          ),
        IF(
           INSTR(t1.country, ' mobile') > INSTR(t1.country, '-'),
           LEFT(t1.country, INSTR(t1.country, ' mobile') - 1),
           LEFT(t1.country, INSTR(t1.country, '-') - 1)
          )
       )
    )
ORDER BY t2.country

Productivity:

country           prefix
Argentina-Mobile  549
Armenia           374
Aruba             297
Ascension Island  247
Australia         61

Answer 2

I posted a live example (in Azure SQL dialect):

https://data.stackexchange.com/stackoverflow/query/4822

, PATINDEX ( MySQL), '-', . - , .

Answer 3

, , (/ , ). , .

select distinct country_name, min(prefix)

Answer 4

MS SQL Server, :

WITH countries AS (
    SELECT 
        LEFT(country, CHARINDEX('-', RTRIM(COUNTRY) + '-') - 1) AS name, 
        LEN(prefix) AS prefixlen 
    FROM
        countryprefix
),
winners as (
    SELECT 
        name, MIN(prefixlen) as shortest
    FROM
        countries
    GROUP BY
        name
)
SELECT 
    country, MIN(prefix)
FROM
    countryprefix cp inner join winners  ON
        LEFT(cp.country, CHARINDEX('-', cp.country + '-') - 1) = winners.name AND
        LEN(prefix) = winners.shortest 
GROUP BY
    country

:

Argentina-Mobile            549
Armenia                 374
Armenia Mobile-K-Telecom    37477
Aruba                   297
Ascension Island            247
Australia                   61

Answer 5

, ,

SELECT country, prefix from countries 
WHERE country LIKE "searchTerm%"
HAVING length(prefix) = min(length(prefix))

I had to do a similar thing (but with the longest prefix) due to the stupidly chosen "occupation" codes at work, where "$ Specialization" and "ist $ Faculty" denote specialists and students, with some additional information ... analyzing these data, you need code like this. YMMV depending on your RDBMS - I tested something v. Similar to this on mysql.