CODEWITHSUNDEEP
sql
Clément Andraud
Clément Andraud

Reputation: 9269

SQL priority condition

I have a query like : 

SELECT * FROM mytable WHERE  (`field1` LIKE '%search%' OR `field2` LIKE '%search%' OR `field3` LIKE '%search%')

Keyword search can be any other words. How can i set priority to this query ? I want to search first in field1, after in field2 and then in field3.

So if I find my keyword search in field1, row with search in field1 must be in first in my results set.

How can i do that ?

Upvotes: 2

Views: 597

Answers (4)

Jean Jung
Jean Jung

Reputation: 1210

You can do it with order by: 

SELECT * 
FROM mytable 
WHERE  (`field1` LIKE '%search%' OR `field2` LIKE '%search%' OR `field3` LIKE '%search%')
ORDER BY 
CASE 
  WHEN `field1` LIKE '%search%' THEN 1
  WHEN `field2` LIKE '%search%' THEN 2
  WHEN `field3` LIKE '%search%' THEN 3
  ELSE 4
END

Upvotes: 3

NizamUlMulk
NizamUlMulk

Reputation: 386

if exists (select top 1 * form mytable where 'field1' like '%search%')
 select * form mytable where 'field1' like'%search%'
else if exists (select top 1* form mytable where 'field2' like '%search%')
 select * form mytable where 'field2' like'%search%'
else
 select * form mytable where 'field3' like'%search%'

Upvotes: 0

sokjukim
sokjukim

Reputation: 56

You'd better split query and union them.

select *
    from (
    SELECT 1 as s, * FROM mytable WHERE  ('field1' LIKE '%search%')
    union
    SELECT 2 as s, * FROM mytable WHERE  ('field2' LIKE '%search%')
    union
    SELECT 3 as s, * FROM mytable WHERE  ('field3' LIKE '%search%')
) a 
order by s

Upvotes: 1

jjanes
jjanes

Reputation: 44137

Order by the boolean result:

SELECT * FROM mytable WHERE
    (
      `field1` LIKE '%search%' OR `field2` LIKE '%search%' 
      OR `field3` LIKE '%search%'
    )
order by `field1` LIKE '%search%' DESC

You need DESC because by default false is usually ordered before true

Upvotes: 0

Related Questions