oracle sql search by preference

Question

Taking a sample table:

WITH t(val) AS
     ( SELECT 'my Name' FROM dual
     UNION
     SELECT 'my name' FROM dual
     UNION
     SELECT 'my naim' FROM dual
     UNION
     SELECT 'MY kat' FROM dual
     UNION
     select 'my katt' from dual
    )
SELECT * FROM t;

I need an output by preference:

The query should search for the exact match in the table first, If not found then search by lowering the case, and if not found then only search for soundex,. So the final output for something like:

WHERE val = 'my Name'
  OR lower(val) = lower('my Name')
  OR soundex(lower(val)) = soundex(lower('my Name'))

should be:

output
-----
my Name

Thanks in advance.

score 1 · Accepted Answer · answered Oct 29 '13 at 12:00

1

Just filter as you describe, then sort by that order, then grab the first record:

WITH t AS
     ( SELECT 'my Name' as val FROM dual
     UNION
     SELECT 'my name' FROM dual
     UNION
     SELECT 'my naim' FROM dual
     UNION
     SELECT 'MY kat' FROM dual
     UNION
     select 'my katt' from dual
    )
SELECT * FROM
(
  SELECT * FROM t
  WHERE val = 'my Name'
  OR lower(val) = lower('my Name')
  OR soundex(lower(val)) = soundex(lower('my Name'))
  order by
    case 
      when val = 'my Name' then 1
      when lower(val) = lower('my Name') then 2
      when soundex(lower(val)) = soundex(lower('my Name')) then 3
    end
)
WHERE ROWNUM = 1;

answered Oct 29 '13 at 12:00

Thorsten Kettner

89,309
7
49
73

Sorry, I don't understand. You are looking for the best match for a name. Thus you either get one record or none. This is what rownum = 1 does. Do you have additional requirements? – Thorsten Kettner Oct 29 '13 at 14:04
1) The order by not make effect for the inner query, 2) the ROWNUM as mentioned. So I've modified with some of your logic , see my answer by myself. – ajmalmhd04 Oct 30 '13 at 03:17
The order by sorts the inner query. The rownum = 1 takes thus the first record of the sorted records of the inner query. I see no reason why this should not work. It works in my dbms (Ora 10g). Doesn't it work in yours? What result do you get when executing the query? – Thorsten Kettner Oct 30 '13 at 10:40
Okay. I'll check again :) – ajmalmhd04 Oct 30 '13 at 11:05
1

Yes, the where clause gets executed before the order by. But as you clearly see in above select statement the inner select has an order by, but no where clause, and the outer select has a where clause, but no order by. So the question wether order by or where gets executed first doesn't apply here. The inner select gets executed before the outer. So the outer gets the first record of the already sorted result set of the inner. – Thorsten Kettner Nov 08 '13 at 09:49
very good explanation. And this is clearly documented in the top-n query of this documentation. http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns009.htm#SQLRF00255 – ajmalmhd04 Nov 08 '13 at 11:52
@Throsten Kettner, See my updated answer. with this link too: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions056.htm – ajmalmhd04 Nov 29 '13 at 11:30

score 1 · Answer 2 · answered Nov 29 '13 at 15:04

For large data sets you'd probably want to avoid the unnecessary tests if any of the earlier ones had found a match.

with t as
   ( SELECT 'my Name' as val FROM dual
   UNION
   SELECT 'my name' FROM dual
   UNION
   SELECT 'my naim' FROM dual
   UNION
   SELECT 'MY kat' FROM dual
   UNION
   select 'my katt' from dual
    )
  exact_match as (
    select *
    from   t
    where  val = 'my Name'),
  lower_case_match as (
    select *
    from   t
    where  lower(val) = lower('my Name') and
           not exists (select null from exact_match)),
  soundex_match as (
    select *
    from   t
    where  soundex(val) = soundex('my Name') and
           not exists (select null from lower_case_match) and
           not exists (select null from exact_match))
select * from exact_match
union all
select * from lower_case_match
union all
select * from soundex_match;

Oracle would most likely materialise the result set of the first two search common table expressions in order to make it more efficient for the subsequent expressions to test whether they returned a result. If the first "exact_match" search returns a result then subsequent searches will not be required to execute.

appriciated. :) Thanks. Let me study the explain plan. and +1 too. Please review my answer with KEEP FIRST dense_rank — ajmalmhd04, Dec 01 '13 at 06:10

Lajos Veres · Answer 3 · 2013-10-29T14:23:09.207

0

Probably you need oracle text, more precisely query relaxation: http://docs.oracle.com/cd/B28359_01/text.111/b28303/query.htm#i1007593

You can imitate something similar like:

select
 *
from
 (select *,
   case
      when condition1 then 3
      when condition2 then 2
      when condition3 then 1
   end relevance
  from
    table
  where
    (condition1 or condition2 or condition3)
  )
order by
  relevance desc

edited Oct 29 '13 at 14:23

answered Oct 29 '13 at 10:59

Lajos Veres

13,595
7
43
56

Thanks for the link, so could you more precise how exact I should use for a sample like given? – ajmalmhd04 Oct 29 '13 at 11:28
Oracle text is a relatively big and not so easy topic. I can't make a few line abstract. Anyway If you think it is overkill then probably you can run the query with the first condition and if it doesn't find anything then with the second condition and so on. Not so nice solution but much more simple, and (developer-)time-efficient. – Lajos Veres Oct 29 '13 at 11:32
Thats what myquery was. – ajmalmhd04 Oct 29 '13 at 11:33
I found a quasi alternative. I modified my answer. – Lajos Veres Oct 29 '13 at 14:23
As the above data is only sample, Your query logic wont work if the excact input is not in the table. I need to get the output for the querying lower case if `=` not work , So i've used rank, see my answer. – ajmalmhd04 Oct 30 '13 at 03:19

ajmalmhd04 · Answer 4 · 2013-11-29T11:29:25.987

How about mine: ( Thanks to @Thorsten Kettner).

SELECT val FROM(
  SELECT val, DENSE_RANK()OVER(ORDER BY CASE WHEN val = 'my Name' THEN 1
                                             WHEN lower(val) = lower('my Name') THEN 2
                                             WHEN soundex(lower(val)) = soundex(lower('my Name')) THEN 3
                                        END
                               )rnk
  FROM t
  WHERE val = 'my Name'
  OR lower(val) = lower('my Name')
  OR soundex(lower(val)) = soundex(lower('my Name'))
)
WHERE rnk  = 1;

Another with FIRST , by Oracle particularly for our purpose:

SELECT MAX(val) keep (dense_rank FIRST ORDER BY priority) AS val
FROM
     (SELECT t.* ,
          CASE WHEN val = 'my Name' THEN 1
               WHEN lower(val) = lower('my Name') THEN 2
               WHEN soundex(lower(val)) = soundex(lower('my Name')) THEN 3
          END priority
     FROM t
     WHERE val                = 'my Name'
       OR lower(val)          = lower('my Name')
       OR soundex(lower(val)) = soundex(lower('my Name'))
     );

Yes, you can use analytic rank functions for that. Just be aware: What if there is no match on the name, but on two soundex matches (say "mi naim" and "my naim" for instance). My solution would give you one of these matches, yours returns both. It's up to you to decide what you prefer. — Thorsten Kettner, Oct 30 '13 at 10:47

oracle sql search by preference

4 Answers4