I am a novice who hasn't mastered the SQL language yet.  Your help will be
appreciated.  I need a query to search a table for records matching one or
more of four conditions and then return them with the most matches first. 

For example,  a table with name, birthdate, gender and zip code.  Search for
records with name = "smith" or birthdate = 19801231 or gender = "m" or zip
code = 30001.  Return how many conditions each record matched and sorted by
the most matches first.  So that I can present all records matching 4 o4 4
criteria, then 3 of 4, 2 of 4 and 1 of 4.

Thanks to all.

select 'results', (
select count(*)  from table where
name = "smith" ) as name  ,
( select count(*)  from table where
gender = "m" ) as gender from table





dtw wrote:
Show quoteHide quote

Build a table with all the patersn you want to match and use soemthing
like this

SELECT F.victim_name, F.birth_date, F.sex_code, F.zip_code,
       M.pattern_nbr,
       (CASE WHEN F.victim_name = M.F.victim_name THEN 1 ELSE 0 END
       + CASE WHEN F.birth_date = M.birth_date THEN 1 ELSE 0 END
       + CASE WHEN F.sex_code = M.sex_code THEN 1 ELSE 0 END
       + CASE WHEN F.zip_code = M.zip_code THEN 1 ELSE 0 END) AS score
  FROM Foobar AS F, MatchList AS M
WHERE (CASE WHEN F.victim_name = M.F.victim_name THEN 1 ELSE 0 END
       + CASE WHEN F.birth_date = M.birth_date THEN 1 ELSE 0 END
       + CASE WHEN F.sex_code = M.sex_code THEN 1 ELSE 0 END
       + CASE WHEN F.zip_code = M.zip_code THEN 1 ELSE 0 END) > 0;

On Fri, 17 Feb 2006 06:16:31 -0800, dtw wrote:

Hi dtw,

SELECT   Name, Birthdate, Gender, Zip, MatchCount
FROM    (SELECT Name, Birthdate, Gender, Zip,
                CASE WHEN Name = 'smith' THEN 1 ELSE 0 END
              + CASE WHEN Birthdate = '19801231' THEN 1 ELSE 0 END
              + CASE WHEN Gender = 'm' THEN 1 ELSE 0 END
              + CASE WHEN Zip = '30001' THEN 1 ELSE 0 END AS MatchCount
         FROM   YourTable) AS X
WHERE    MatchCount >= 1
ORDER BY MatchCount DESC

(untested - see www.aspfaq.com/5006 if you prefer a tested reply)

OBJECTPROPERTY problem
T-SQL: How to loop through the resultset from another procedure?
Count Occurances in a column
Who has the highest bid?
Need Help Please...