Re: SQL Strategy

From: Vicente Barreiro <vbarreiro_at_mediaone.net>
Date: Wed, 14 Mar 2001 03:44:33 GMT
Message-ID: <BOBr6.82$p_2.11783@typhoon.mn.mediaone.net>

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

.
.
.

Select "valn" Key, <your fields>
from your_table
where coln = valn

Then execute that into a temporary table or cursor or whatever and then execute something like

Select <your fields>, count(Key) Hits
from TempTable
Group by <your fields>
Order by Hits

This will give you your matches and the "score" of the result. Note that the idea of the Key field is that they are different between the UNION so similar rows are not merged together.

If you find a better solution let me know, I'm curious...

Vicho.

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
>
> I would prefer to do this in a single query (without too many sub queries)
> since the table we are hitting will be rather large) Also, are there any
> build in methods of SQL functions in Oracle that would allow me to do
 this?
>
> Any and all suggestions would be extremely appreciated.
>
> Craig
>
>
Received on Tue Mar 13 2001 - 21:44:33 CST