How to find "best" matches

How do I search a table for the best match. For example... I have a name,
zip, birthday and gender column. How do I search for name = smith, zip =
30001, birthday = 19501201 and gender = m and list those records matching 4
of 4, 3 of 4, 2 of 4 or 1 of 4 of the criteria?
--
David"dtw" <dtw@.discussions.microsoft.com> wrote in message
news:B6971108-5543-4069-931A-DFC88E1A49A4@.microsoft.com...
> How do I search a table for the best match. For example... I have a name,
> zip, birthday and gender column. How do I search for name = smith, zip =
> 30001, birthday = 19501201 and gender = m and list those records matching
> 4
> of 4, 3 of 4, 2 of 4 or 1 of 4 of the criteria?
> --
> David
Here's one way... maybe not the best:
select ...,
case name when 'smith' then 1 else 0 end
+ case zip when '30001' then 1 else 0 end
+ case birthday when '19501201' then 1 else 0 end
+ case gender when 'm' then 1 else 0 end AS match
from tableName
order by match desc
You can filter the results to return only records where there is at least
one match.|||You can implement a calculated column called [score] by adding the result of
several case functions like so:
select
*,
(
case when name = 'smith' then 1 else 0 end +
case when zip = 30001 then 1 else 0 end +
case when birthday = '19501201' then 1 else 0 end +
case when gender = 'm' then 1 else 0 end
) as score
from
mytable
This will result in each row being assigned a score from 0 - 4.
Also, if you want to filter, group or order a query based on a complex
expression (and I'm sure you will), then you can avoid repeating the
expression within the [where], [group by], and [order by] clause, by
implementing a sub-query like so:
select
score,
count(*) as cnt
from
(
select
(
case when name = 'smith' then 1 else 0 end +
case when zip = 30001 then 1 else 0 end +
case when birthday = '19501201' then 1 else 0 end +
case when gender = 'm' then 1 else 0 end
) as score
from
mytable
) as x
where
score >= 2
group by
score
order by
score desc
An alternative to the sub-query is to implement a temporary table.
"dtw" <dtw@.discussions.microsoft.com> wrote in message
news:B6971108-5543-4069-931A-DFC88E1A49A4@.microsoft.com...
> How do I search a table for the best match. For example... I have a name,
> zip, birthday and gender column. How do I search for name = smith, zip =
> 30001, birthday = 19501201 and gender = m and list those records matching
> 4
> of 4, 3 of 4, 2 of 4 or 1 of 4 of the criteria?
> --
> David