• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

Bestmatch Top-Row select

Hi there!

I am currently selecting the bestmatch in an old-fashioned kind of way:

select t1.x,t2.y,t2.z from t1, t2 where t1.x like concat(t2.y,'%') order by t2.y desc;

x is filled with telephonenumbers whereas y is populated with areacodes of different length, like 702,7025,70255,702555 and z holds the corresponding cityname for y.

Now my question is that I don't want to get ALL the possible matches with the bestmatch as first row, but I only want to get this one row as result. Currently my problem is that I always get each x-number as many times as there are y-entries and that is definitely causing problems.

As always and as you have already expected: this is a rather urgent matter and that's why this is valued at 500 points :-)


Thanks for your help!


Best regards,


Tillman
0
tillmanz123
Asked:
tillmanz123
  • 3
  • 3
1 Solution
 
star_trekCommented:
I don't think you can do it with MySQL, you have to extrapolate your data and use some kind of scripting to display in the format you need.
0
 
tillmanz123Author Commented:

I give you a small example:

t1:
x-column - a-column
12125551212 - 2006-01-01
13104451255 - 2006-01-02
12122224545 - 2006-01-03

t2:
y-column - z-column
1 - USA
1212 - NY
1212555 - DirectoryAssistence

select t1.x, t1.a, t2.z
from t1, t2
where t1.x like concat(t2.y,'%')
order by t2.y desc;

I want my result to look like this:

12125551212,2006-01-01,DirectoryAssistence
13104451255,2006-01-02,USA
12122224545,2006-01-03,NY

Maybe I am just too blind to see, but please tell me how to do that (apart from using my application to parse through the result set).


Best regards,

Tillman
0
 
virmaiorCommented:
it would help to know which version of mysql that you are using.

my first suggestion would be this:

CREATE table `hold_it`
select t1.x, t1.a, t2.y, t2.z
from t1, t2
where t1.x like concat(t2.y,'%')
order by t2.y desc;

SELECT x, a, z FROM `hold_it` GROUP BY x

though the results of that query will technically be arbitrary



0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
star_trekCommented:
try this
select x,y,z from (select t1.x as x,t2.y as y,t2.z as z from t1, t2 where t1.x like concat(t2.y,'%') order by t2.y desc) as d group by x;
0
 
virmaiorCommented:
which is nothing more than what i suggestd rewritten as a subselect...
my code will work in 3.23+, his code will only work in 4.1+ and is substantially identical... which is especially funny since his initial comment is "I don't think mysql can do this"
0
 
star_trekCommented:
virmajor, i agree with you, it is same as yours except with the sub query.
Also about about my first comment i wasn't clear about the tillman's result, until he explained in his later post.
0
 
virmaiorCommented:
fair enough
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now