Taleh Ibrahimli Taleh Ibrahimli - 10 months ago 61
MySQL Question

SQL statement optimization issue

I want to show rows where region = 'sample' as first rows in result. I mean priority must be higher than another rows and they must be at the top.

I use the SQL below for this result.

SELECT * FROM works Where region = 'sample' UNION Select * FROM works Where region <> 'sample'

Is there any optimal way to get result without UNION?

Answer Source

First of all Union will not guarantee order. Even if you do it like that, there are chances not to be in the right order. So you have to do it with an order by clause.


select * from work order by case when region='sample' then 0 else 1 end

better if you have an index on region

select *,0 as ordb from work where region = 'sample'
select *,1 as ordb from work where region <> 'sample'
order by ordb