Mysql Order By Field With %

I am trying to make an ORDER BY FIELD work with a wildcard, and have been unsuccessful: SELECT positions.*, departments.dept_name, departments.dept_url, div

Solution 1:

This should give you the most control over it:

orderbycaseleft(positions.colleague_position_id, 1)
    when'A'then1when'F'then2when'T'then3when'S'then4when'C'then5else6end, positions.colleague_position_id

This is because you can send all non-matching values to the position you want (in this case at the end). The field() function will return 0 for non matching values and will put them at the top of the result set even before the ones starting with A.

Additionally, you can also order by positions.colleague_position_id as I did in the example, so that for many positions.colleague_position_id that start with the same letter they will still be in order.

Solution 2:

How about removing the WildCard?

ORDER BY FIELD(positions.colleague_position_id, 'A', 'F', 'T', 'S', 'C')

See Source: ORDER BY SPECIFIC VALUE