CODEWITHSUNDEEP
phpmysql
MrMime
MrMime

Reputation: 715

Mysql - Order By in IN Clausole

I have a simple question. I have to join multiple queries and most of them has ad IN clausole. Unfortunally, MySQL doesn't allow ORDER BY in an UNION (only outside all unions) but I need a specific ordering that I can't get with an outside order by.

Question is: If I have a IN clausole like 

foo in ('newyork','boston','atlanta')

may I assume MySql's engine will order the resulting rows by IN position (so, first all the row with foo = 'newyork' then 'boston' etc...

Thanks.

Upvotes: 0

Views: 55

Answers (2)

jAi
jAi

Reputation: 115

Select *
from
(
  Select * from Temp1
  order by x
) Tbl1
UNION
Select *
from
(
  Select * from Temp2
  order by y
) Tbl2

Try this.

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

No. It's nothing like that. You need to explicitly add an order by clause.

If you want to order your rows based on position in a set, you can use field function.

order by field(foo,'newyork','boston','atlanta')

Upvotes: 2

Related Questions