Re: SQL Strategy

>Craig Despres <craig_at_vtpartners.com> wrote in message
>news:L9zr6.25618$Xt3.3843102_at_news1.rdc1.az.home.com...
>> I have a question on the best function, tool, query or method to
 accomplish
>> the following:
>>
>> Let's say I have a table with col1, col2, col3
>>
>> And I want to return all rows where any or all of my criteria match and I
>> would like the results sorted in order of how many criteria matched. So,
>> for example:
>>
>> if my search criteria is: col1 = val1, col2 = val2, and col3 = val3
>>
>> I want all rows where all 3 match listed first,
>> then all rows where 2 of the 3 match
>> then all rows where at least one match

On Wed, 14 Mar 2001 03:44:33 GMT, "Vicente Barreiro" <vbarreiro_at_mediaone.net> wrote:

>How about dynamically generating a Sql statment like
>
>Select "val1" Key, <your fields>
>from your_table
>where col1 = val1
>UNION
>Select "val2" Key, <your fields>
>from your_table
>where col2 = val2

An idea to do it in one query might be:

select	your_table.*,
	decode(col1, val1, 1) +
	decode(col2, val2, 1) +
	decode(col3, val3, 1) "SCORE"
from	your_table
order by decode(col1, val1, 1) +
	decode(col2, val2, 1) +
	decode(col3, val3, 1) desc

Although decode is usually pretty fast, I have no guarantees that this is going to be the speediest query ever.

Hope this helps

Gary

--
Gary O'Keefe
gary_at_onegoodidea.com
+44 (0) 7976 614 336