Re: SQL Strategy

I tried the following, it seems to work:

SQL> select * from t;

     COL1 COL2 COL3
--------- --------- ---------

        1         2         3
        1         2         3
        1         2
        1         2
                  2         3
                  2         3
                            3
                            3
        2         2         2
        3         3         3
        1         1         1
        1         2         5
        1         5         3
        5         2         3

14 rows selected.

SQL> select * from t where nvl(col1,0)
in (1,0) and
  2 nvl(col2,0) in (2,0) and
nvl(col3,0) in (3,0) order by col1,
col2, col3;

     COL1 COL2 COL3
--------- --------- ---------

        1         2         3
        1         2         3
        1         2
        1         2
                  2         3
                  2         3
                            3
                            3

8 rows selected.

--
A.M. Andreyev,
ORACLE DBA
"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

>

>