Need help for generating sql for parametric search

[mabus]

I would like to perform a query on a table (or group of tables) based on multiple parameters. I do not need the results to match all these parameters; matching at least one of the parameters is enough. However, I want to rank the results in such a way that those that match more parameters rank higher than those that match less parameters. Also, I would like to define a "weight" to each of the parameter to help sort the results.

 

For example, say I have a Person table and did a search for: Gender = Male and Eye Color = Blue.

 

Assume I have 4 records:

 

1. Gender = Male, Eye Color = Blue

2. Gender = Female, Eye Color = Blue

3. Gender = Male, Eye Color = Green

4. Gender = Female, Eye Color = Green

 

The result should give me records #1, #2, and #3.

 

Then let's say that I wanted to give more weight to Gender than Eye Color. Thus, my expected result would be this: #1, #3, and #2.

 

How would I accomplish this?

[fenway]

For example, say I have a Person table and did a search for: Gender = Male and Eye Color = Blue.

 

.....

 

The result should give me records #1, #2, and #3.

Assuming you mean logical AND and not database AND.

 

If you want to weight it, then just SUM() up successive IF() expressions that give different weights.

[mabus]

Hey thanks. Can I see some samples of that query?

[fenway]

Like this:

 

IF( gender = 'male', 10, 0 ) + IF( eye_color = 'blue', 5, 0 )