title: Find longest match number on another table
body: <p>Given I have two tables - routes and country_codes.
  Table routes contains column <code>prefix</code> which I want to match to the longest value found in table country_codes column <code>code</code></p>
  
  <p>The objective is to find the country name of each route.</p>
  
  <p>The data structure I have</p>
  
  <pre><code>CREATE TABLE routes(
     prefix   INTEGER  NOT NULL,
     supplier VARCHAR(64) NOT NULL
  );
  </code></pre>
  
  <p>table routes</p>
  
  <pre><code>prefix      supplier
  1876        att
  1787        att
  1           att
  81          bt
  8150        bt
  8170        bt
  8180        bt
  8190        bt
  82          verizon
  821         verizon
  84          att
  84120       att
  84121       att
  84122       att
  84123       att
  84124       att
  84125       att
  85248       verizon
  85249       verizon
  85251       verizon
  
  
  CREATE TABLE country_codes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      country VARCHAR(128) DEFAULT 0 NOT NULL,
      code INTEGER
  );
  </code></pre>
  
  <p>table country_codes</p>
  
  <pre><code>167 American Samoa              1684
  170 Anguilla                    1264
  173 Antigua and Barbuda         1268
  181 Bahamas                     1242
  184 Barbados                    1246
  189 Bermuda                     1441
  195 British Virgin Islands      1284
  205 Cayman Islands              1345
  225 Dominica                    1767
  226 Dominican Republic          1809
  227 Dominican Republic          1829
  228 Dominican Republic          1849
  251 Grenada                     1473
  253 Guam                        1671
  273 Jamaica                     1876
  310 Montserrat                  1664
  326 Northern Mariana Islands    1670
  340 Puerto Rico                 1787
  341 Puerto Rico                 1939
  350 Saint Kitts and Nevis       1869
  351 Saint Lucia                 1758
  354 Saint Vincent and the Grenadines    1784
  364 Sint Maarten                1721
  389 Trinidad and Tobago         1868
  393 Turks and Caicos Islands    1649
  399 United States               1
  401 US Virgin Islands           1340
  274 Japan                       81
  370 South Korea                 82
  405 Vietnam                     84
  201 Cambodia                    855
  261 Hong Kong                   852
  282 Laos                        856
  291 Macau                       853
  325 North Korea                 850
  </code></pre>
  
  <p>So I want to query table <code>routes</code> to give me result prefix, country</p>
  
  <p>results I am looking for</p>
  
  <pre><code>prefix      country
  1876        Jamaica
  1787        Puerto Rico
  1           United States
  81          Japan
  8150        Japan
  8170        Japan
  8180        Japan
  8190        Japan
  82          South Korea
  821         South Korea
  84          Vietnam
  84120       Vietnam
  84121       Vietnam
  84122       Vietnam
  84123       Vietnam
  84124       Vietnam
  84125       Vietnam
  85248       Hong Kong
  85249       Hong Kong
  85251       Hong Kong
  </code></pre>
  
  <p>What sql query can i use to do this?</p>
  
  <p>The complication its not a straight match that can be done with a join.
  Its longest match needs to be used.</p>