Re: SQL Strategy

From: Simon Irvin <sirvin_at_synomics.com>
Date: Wed, 14 Mar 2001 16:20:04 -0000
Message-ID: <3aaf9ab9$1_2@nnrp1.news.uk.psi.net>

One way to do this would be to create a function (see below) which generates the number of hits per row and use a select which 'OR's the three search values and orders the result_set.
The select would be something like

select

  col1,
  col2,
  col3,

  search_ranking(col1,'AAA',col2,'BBB',col3,'CCC') as search_ranking from
  tableA
where

  col1 = 'AAA' or
  col2 = 'BBB' or
  col3 = 'CCC'

order by
  ranking desc;

and the function could be:

CREATE OR REPLACE FUNCTION "SCOTT"."SEARCH_RANKING" (  col1 VARCHAR2,
 value1 VARCHAR2,
 col2 VARCHAR2,
 value2 VARCHAR2,
 col3 VARCHAR2,
 value3 VARCHAR2)
 RETURN PLS_INTEGER AS
 ranking_score PLS_INTEGER;
BEGIN
 ranking_score := 0;
 if col1 = value1 then

    ranking_score := ranking_score + 1;
 end if;
 if col2 = value2 then

    ranking_score := ranking_score + 1;
 end if;
 if col3 = value3 then

    ranking_score := ranking_score + 1;
 end if;
 return ranking_score;
END;  "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 Wed Mar 14 2001 - 10:20:04 CST