Specified record in top of results list

Question

Task: selecting specified record in top of results list

my ugly variant:

(select * from mytable where id = 42)
union all
(select * from mytable where id != 42 order by id)

besides of ansi query variant for mysql is also would be very interesting to me

Answer 1

There is no difference between ANSI and vendor SQL solutions

select *
from mytable 
order by
    CASE WHEN id = 42 THEN 0 ELSE 1 END, id

Note: there is no guaranteed or implied order to a table or SELECT without an ORDER BY clause