0

I have MYSQL query:

SELECT * FROM  user WHERE name LIKE '%ana%' 
OR name LIKE '%adam%' 
OR name LIKE '%pit%'

Is any way to order result by OR sequence

I mean to display results with name LIKE '%ana%' as first name LIKE '%adam%' as second name LIKE '%pit%' as third.

Szmulik
  • 241
  • 1
  • 4
  • 10
  • possible duplicate of [How to define a custom ORDER BY order in mySQL](http://stackoverflow.com/questions/9378613/how-to-define-a-custom-order-by-order-in-mysql) – Alex K. Jul 27 '15 at 14:47
  • i think you need to decide if it is A) =, or B) Like – Drew Jul 27 '15 at 15:05
  • I think the only think "like" is doing here is that it makes the comparison case insensitive. Is that what you meant? –  Jul 27 '15 at 15:26

2 Answers2

2

First, use IN for the where clause (you don't have wildcards, so this is actually more efficient):

where name in ('ana', 'adam', 'pit')

Second, use find_in_set() for the ordering:

order by find_in_set(name, 'ana', 'adam', 'pit')
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • It's obviously more elegant, but is it actually more efficient too? – Strawberry Jul 27 '15 at 14:49
  • not much of a like :> ... that said, i am upvoting, as it shows the community something ! – Drew Jul 27 '15 at 15:04
  • It looks good, Can use this for modified query SELECT * FROM user WHERE name LIKE '%ana%' OR name LIKE '%adam%' OR name LIKE '%pit%' – Szmulik Jul 27 '15 at 16:04
  • @Szmulik . . . No. In that case, you would need an explicit `case` to order the rows (or perhaps use a secondary table). – Gordon Linoff Jul 27 '15 at 16:59
  • @Strawberry . . . Yes, in general. MySQL optimizes `in` with a list of constants by sorting the constants. Okay, I admit that this probably has zero effect for three items, but in general, `IN` is more efficient than the equivalent `=` connected by `OR`. (And MySQL is the only database that I know of that does this optimization.) – Gordon Linoff Jul 27 '15 at 17:01
0

you can do something like

order by if(name like 'ana', 1, if(name like 'adam', 2, 3))