CODEWITHSUNDEEP
sqlmysql
veccy
veccy

Reputation: 925

mysql shortest length match

I dont know how to explain what I want other 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 on the prefix to get a list of the shortest parent prefix

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

Upvotes: 3

Views: 1259

Answers (5)

Cade Roux
Cade Roux

Reputation: 89671

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

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

Note that this uses the PATINDEX (this is not portable to MySQL) to find the first occurrence of a space or '-' to classify the countries first. Then it finds the shortest within the class - then joins back to get the result.

Upvotes: 0

tobyodavies
tobyodavies

Reputation: 28099

assuming prefix is a string,

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

I had to do a similar thing (but with longest prefix) because of stupidly chosen 'occupation' codes at work where "is$Specialization" and "ist$Faculty" denote specalists and students, with some extra info... analysing that data required code similar to this. YMMV depending on your RDBMS - I tested something v. similar to this on mysql.

Upvotes: 0

smirkingman
smirkingman

Reputation: 6368

This is MS SQL Server, but the idea is there:

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

Output:

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

Upvotes: 0

twomasc
twomasc

Reputation: 11

I think you could come a long way by normalizing the country into it's own field (and/or table, with a country id). Probably be helpful in the long run.

Then you would just have to do a simple 

select distinct country_name, min(prefix)

Upvotes: 0

Marcus Adams
Marcus Adams

Reputation: 53850

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

This would be a lot easier though, and less error prone if you had a country code column for each field, rather than having to parse the country text, which may 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

Yields:

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

Upvotes: 1

Related Questions