September 14, 2010 at 8:04 am
All,
I need to build a query (in fact a stored procedure) with a special behavior.
The matching on certain criteria is mandatory, while the matching on other criteria will give some kind of score.
Let's consider an example:
table tbl_test (key, field1, field2, field3, field4, field5, field6)
Suppose I set:
1) field1 and field2 matching is mandatory
2) field3 ... field6 matching is "optional" but gives a score (1 point per matching criteria).
Then, I sort the result based on the "score DESC".
Data example:
key, field1, field2, field3, field4, field5, field6
1, 1, 1, 1, 0, 0, 4
2, 1, 1, 0, 2, 1, 3
3, 1, 2, 1, 0, 1, 2
4, 2, 1, 1, 2, 1, 1
5, 2, 1, 1, 0, 1, 1
6, 1, 2, 1, 1, 1, 2
7, 2, 2, 1, 0, 0, 3
8, 1, 1, 1, 1, 0, 1
9, 1, 1, 1, 0, 1, 2
Constraints:
Exclusion criteria: field1= 1 and field2 = 1
Optional criteria: field3 = 1, field4 < 2, field 5=1, field6<3
Results:
1) Exclusion criteria will return keys: 1, 2, 8 and 9
2) Optional criteria will give as individual score for these keys:
Key1: 2 (field3: OK, field4: OK, field5: not OK, field6: not OK)
Key2: 2 (field3: not ok, field4: OK, field5: OK, field6: not OK)
Key8: 3 (field3: OK, field4: OK, field5: not OK, field6: OK)
Key9: 4 (field3: OK, field4: OK, field5: OK, field6: OK)
Final Result:
Result will then be in the following sequence order:
Key9
Key8
Key1
Key2
Does anyone know how to do this?
Many thanks for your help
Didier
September 14, 2010 at 8:24 am
didier.boelens
You increase the chance of receiving a tested answer to your question if you post the table definition and sample data and what you have attempted in accordance with the article whose link is the first line in my signature block.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply