I'm trying to write an SQL query that has "OR" operator. The thing is that I want it to work with some, let say "priorities". I have an entity Item, it has two fields that I use in search:
And there is an SQL query:
select * from item
where description like '%a%' or title like '%a%';
What I want here is that if we have two entities returned and one of them matches like '%a%' by description and another one - by title, the one that matches via title should be the first one in the list. In other words, it should have bigger priority. Is there a way I can describe such a purpose in SQL?
Dialect: Oracle / H2
In Oracle, you may use a CASE to order by a values that makes rows ordered by the way they match your conditions:
/* test case */
with item(title, description) as (
select '__x__', '__x__' from dual union all
select '__x__', '__a__' from dual union all
select '__a__', '__x__' from dual union all
select '__a__', '__a__' from dual
)
/* the query */
select *
from item
where description like '%a%' or title like '%a%'
order by case
when title like '%a%'
then 1
else 2
end
This gives:
TITLE DESCR
----- -----
__a__ __x__
__a__ __a__
__x__ __a__