selecting the "best" match

--000e0cd242328868b20469b90efb
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

I have a hold car data such as color, model, make, year, etc. I want to
allow the user to answer some questions and I'll present them with the car
that 'best' matches their criteria. How do I do this? I still want to
return ones that don't match exactly but want the closer matches ordered at
the top:

Table:cars

columns: car_id, make, model, year, color, condition

So if the user enterrs:

model: Toyota
year: 1998
condition:great
color: blue

I would show them a blue 1998 good conditioned camry first but farther down
in the list might still have a blue good condition 98 Honda.

Thanks!

--000e0cd242328868b20469b90efb--

Re: selecting the "best" match

--00163662e5c6314c120469b9820e
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

you *could* go with if-statements, returning a numerical weight for each
criterion if match and 0 if not; summing those and sorting by the sum
column.

I would do it in code, though - it may or may not be less efficient, but
it'll be easier to maintain and read.


On Tue, May 12, 2009 at 5:50 PM, blackwater dev wrote:

> I have a hold car data such as color, model, make, year, etc. I want to
> allow the user to answer some questions and I'll present them with the car
> that 'best' matches their criteria. How do I do this? I still want to
> return ones that don't match exactly but want the closer matches ordered at
> the top:
>
> Table:cars
>
> columns: car_id, make, model, year, color, condition
>
> So if the user enterrs:
>
> model: Toyota
> year: 1998
> condition:great
> color: blue
>
> I would show them a blue 1998 good conditioned camry first but farther down
> in the list might still have a blue good condition 98 Honda.
>
> Thanks!
>



--
Celsius is based on water temperature.
Fahrenheit is based on alcohol temperature.
Ergo, Fahrenheit is better than Celsius. QED.

--00163662e5c6314c120469b9820e--

Re: selecting the "best" match

blackwater dev wrote:
> I have a hold car data such as color, model, make, year, etc. I want to
> allow the user to answer some questions and I'll present them with the car
> that 'best' matches their criteria. How do I do this? I still want to
> return ones that don't match exactly but want the closer matches ordered at
> the top:
>
> Table:cars
>
> columns: car_id, make, model, year, color, condition
>
> So if the user enterrs:
>
> model: Toyota
> year: 1998
> condition:great
> color: blue
>
> I would show them a blue 1998 good conditioned camry first but farther down
> in the list might still have a blue good condition 98 Honda.
>
> Thanks!
> Perhaps:
SELECT * FROM cars order by model!='Toyota',model;


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: selecting the "best" match

--0016364edc54a985fc0469b9f74b
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Thanks but doing it in code would require me to pull in the entire car table
and process it. With potentially tons of rows, seems like I should be able
to use the db to get those.

On Tue, May 12, 2009 at 12:23 PM, Johan De Meersman wrote:

> you *could* go with if-statements, returning a numerical weight for each
> criterion if match and 0 if not; summing those and sorting by the sum
> column.
>
> I would do it in code, though - it may or may not be less efficient, but
> it'll be easier to maintain and read.
>
>
>
> On Tue, May 12, 2009 at 5:50 PM, blackwater dev wrote:
>
>> I have a hold car data such as color, model, make, year, etc. I want to
>> allow the user to answer some questions and I'll present them with the car
>> that 'best' matches their criteria. How do I do this? I still want to
>> return ones that don't match exactly but want the closer matches ordered
>> at
>> the top:
>>
>> Table:cars
>>
>> columns: car_id, make, model, year, color, condition
>>
>> So if the user enterrs:
>>
>> model: Toyota
>> year: 1998
>> condition:great
>> color: blue
>>
>> I would show them a blue 1998 good conditioned camry first but farther
>> down
>> in the list might still have a blue good condition 98 Honda.
>>
>> Thanks!
>>
>
>
>
> --
> Celsius is based on water temperature.
> Fahrenheit is based on alcohol temperature.
> Ergo, Fahrenheit is better than Celsius. QED.
>

--0016364edc54a985fc0469b9f74b--

Re: selecting the "best" match

--001636c5c1427280f50469ba35a4
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

not if you did it in a stored procedure. Given that it's gonna be pretty
hard to use indices on this anyway (I think), you're gonna scan the entire
table anyway. That's what you get for fuzzy searches.

On Tue, May 12, 2009 at 6:55 PM, blackwater dev wrote:

> Thanks but doing it in code would require me to pull in the entire car
> table
> and process it. With potentially tons of rows, seems like I should be able
> to use the db to get those.
>
> On Tue, May 12, 2009 at 12:23 PM, Johan De Meersman > >wrote:
>
> > you *could* go with if-statements, returning a numerical weight for each
> > criterion if match and 0 if not; summing those and sorting by the sum
> > column.
> >
> > I would do it in code, though - it may or may not be less efficient, but
> > it'll be easier to maintain and read.
> >
> >
> >
> > On Tue, May 12, 2009 at 5:50 PM, blackwater dev > >wrote:
> >
> >> I have a hold car data such as color, model, make, year, etc. I want to
> >> allow the user to answer some questions and I'll present them with the
> car
> >> that 'best' matches their criteria. How do I do this? I still want to
> >> return ones that don't match exactly but want the closer matches ordered
> >> at
> >> the top:
> >>
> >> Table:cars
> >>
> >> columns: car_id, make, model, year, color, condition
> >>
> >> So if the user enterrs:
> >>
> >> model: Toyota
> >> year: 1998
> >> condition:great
> >> color: blue
> >>
> >> I would show them a blue 1998 good conditioned camry first but farther
> >> down
> >> in the list might still have a blue good condition 98 Honda.
> >>
> >> Thanks!
> >>
> >
> >
> >
> > --
> > Celsius is based on water temperature.
> > Fahrenheit is based on alcohol temperature.
> > Ergo, Fahrenheit is better than Celsius. QED.
> >
>



--
Celsius is based on water temperature.
Fahrenheit is based on alcohol temperature.
Ergo, Fahrenheit is better than Celsius. QED.

--001636c5c1427280f50469ba35a4--

Re: selecting the "best" match

--------------050207030200020401050603
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


in your code, you can define ranges of say if the model year being
looked for is 2002, then present model years 2000 thru 2004.

--Curtis

blackwater dev wrote:
> Thanks but doing it in code would require me to pull in the entire car table
> and process it. With potentially tons of rows, seems like I should be able
> to use the db to get those.
>
> On Tue, May 12, 2009 at 12:23 PM, Johan De Meersman wrote:
>
>
>> you *could* go with if-statements, returning a numerical weight for each
>> criterion if match and 0 if not; summing those and sorting by the sum
>> column.
>>
>> I would do it in code, though - it may or may not be less efficient, but
>> it'll be easier to maintain and read.
>>
>>
>>
>> On Tue, May 12, 2009 at 5:50 PM, blackwater dev wrote:
>>
>>
>>> I have a hold car data such as color, model, make, year, etc. I want to
>>> allow the user to answer some questions and I'll present them with the car
>>> that 'best' matches their criteria. How do I do this? I still want to
>>> return ones that don't match exactly but want the closer matches ordered
>>> at
>>> the top:
>>>
>>> Table:cars
>>>
>>> columns: car_id, make, model, year, color, condition
>>>
>>> So if the user enterrs:
>>>
>>> model: Toyota
>>> year: 1998
>>> condition:great
>>> color: blue
>>>
>>> I would show them a blue 1998 good conditioned camry first but farther
>>> down
>>> in the list might still have a blue good condition 98 Honda.
>>>
>>> Thanks!
>>>
>>>
>>
>> --
>> Celsius is based on water temperature.
>> Fahrenheit is based on alcohol temperature.
>> Ergo, Fahrenheit is better than Celsius. QED.
>>
>>
>
>


--------------050207030200020401050603--

Re: selecting the "best" match

Hi!


blackwater dev wrote:
> I have a hold car data such as color, model, make, year, etc. I wa=
nt to
> allow the user to answer some questions and I'll present them with =
the car
> that 'best' matches their criteria. How do I do this? I still wan=
t to
> return ones that don't match exactly but want the closer matches or=
dered at
> the top:
>=20
> Table:cars
>=20
> columns: car_id, make, model, year, color, condition
>=20
> So if the user enterrs:
>=20
> model: Toyota
> year: 1998
> condition:great
> color: blue
>=20
> I would show them a blue 1998 good conditioned camry first but fart=
her down
> in the list might still have a blue good condition 98 Honda.

Returning records in some specified order requires to sort them, and =
for
this you need some criteria.
In most cases, these are values of these records (like a name or size=
),
but this doesn't match your application.

You want to order by a "distance": How much deviates this record from
the perfect match?

Mathematically, your records are points in some n-dimensional space
(dimensions being "model", "year", "condition", "color", probably
several others), and your "perfect" match is one specific such point.

If all your dimensions were numerical (like year) or at least ordered
(like condition: broken, poor, average, good, great), you could defin=
e a
distance within each dimension, and then combine these to n-dimension=
al.
Comparison to geometry: When you know the distances in x, y, and z
dimension, you can use Pythagoras' formula:
dist =3D sqrt ( (x1 - x)**2 + (y1 - y)**2 + (z1 - z)**2 )

But with non-numerical and even un-ordered data, your choices are pre=
tty
arbitrary:
What is the "distance" between colors?
Which one is "closer" to a Toyota - a Ford or a Volkswagen?
So I doubt you can define a "closeness" (or "distance") function.


In your case, I propose to try UNION:
SELECT ... WHERE model =3D UserModel AND color =3D UserColor
ORDER BY condition, ABS (year - UserYear)
UNION
SELECT ... WHERE model =3D UserModel AND color !=3D UserColor
ORDER BY condition, ABS (year - UserYear)
UNION
SELECT ... WHERE model =3D !UserModel AND color =3D UserColor
ORDER BY condition, ABS (year - UserYear)
UNION
SELECT ... WHERE model =3D !UserModel AND color =3D !UserColor
ORDER BY condition, ABS (year - UserYear)

This is based on the assumption that the exact match on the model is
more important than on the color.
If your experience with buyers differs, adapt the order.
And if you think a mismatch in both model and color makes the car
irrelevant, drop the last SELECT in the UNION.


But as soon as the potential buyer says "doesn't matter", you would n=
eed
a different SELECT (one that doesn't differ by that field), so the wh=
ole
thing calls for a program that generates the whole SQL statement base=
d
on the user input.


HTH,
Jörg

--=20
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
(+49 30) 417 01 487
Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB1610=
28


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg