MySQL Forums
Forum List  »  Data Warehouse

Re: Best Match using MySql
Posted by: Rick James
Date: December 29, 2009 12:16AM

Assuming the 'other table' has lots of numbers and you want to find them all...
SELECT *
   FROM (
      SELECT n.number, p.Rate
         FROM Prefix p, Numbers n
         WHERE LEFT(n.number, LENGTH(p.Prefix)) = p.Prefix
         ORDER BY n.number, p.Rate ASC
        ) x
    GROUP BY n.number;
# or...  If it works, this should be faster (if you have an index on Prefix)
SELECT *
   FROM (
      SELECT n.number, p.Rate
         FROM Prefix p, Numbers n
         WHERE Prefix LIKE CONCAT(n.number, '%')
         ORDER BY n.number, p.Rate ASC
        ) x
    GROUP BY n.number;

If you have must one number, the code is much simpler:
SELECT MIN(p.Rate)
    FROM Prefix p, Numbers n
    WHERE Prefix LIKE CONCAT(n.number, '%');

If you did not want MIN, but wanted the 'first':
SELECT p.Rate
    FROM Prefix p, Numbers n
    WHERE Prefix LIKE CONCAT(n.number, '%')
    ORDER BY <<whatever controls the order of Prefix table>>
    LIMIT 1;

Options: ReplyQuote


Subject
Views
Written By
Posted
9063
December 27, 2009 09:51PM
Re: Best Match using MySql
5243
December 29, 2009 12:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.