Method to sort MYSQL results by an arbitriary letter (ie, show all rows starting with F first)

Question

I have a column of states, and, depending on the query, I want to order by results by a particular state, then by id (Asc or Desc, depending). For instance, I might want to show all rows with state "HI", sorted by ID desc, and then all the other rows, sorted by id desc.

I was hoping I could do this in one query, rather than getting all my favored state results, and then getting the rest. Can I?

Answer 1

You have two options:

• do a union
• write a function and use it to order rows by

In the first case, you could do something like

select 1 as res_order, ...
  ...
  where state like 'hi%'
union
select 2 as res_order, ...
  ...
  where state not like 'hi%'
order by res_order asc, id desc

In the second case, you could do something like

select my_function(state, "hi") as row_order, ...
  ...
  order by row_order

where the function returns lower values for matching states.

The code is off the top of my head: it might need some tweaking to make it runnable.

Answer 2

How about:

SELECT id, state
FROM sometable
ORDER BY IF(state = 'HI', 0, 1) ASC, id DESC;

This will sort 'HI' rows first. If you want them last, change the ASC to DESC.