MySQL ORDER BY FIELD with %

Question

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, 
       divisions.dept_name AS div_name
FROM positions LEFT JOIN departments 
           ON positions.colleague_dept_code = departments.colleague_code 
     LEFT JOIN departments AS divisions 
          ON positions.colleague_div_code = divisions.colleague_code
WHERE colleague_id = '$colleague_id'
ORDER BY FIELD(positions.colleague_position_id, 'A%', 'F%', 'T%', 'S%', 'C%')

The colleague_position_id field has a text ID generated by our MIS system, and I'd like for positions starting with A to display first, F to display second, etc., etc.

Any help you could provide would be greatly appreciated.

Thanks!

Can you create a separate table for your positions and have a 'rank' column as well? Then you can join with your existing query and order by positrion.rank. — Chetter Hummin, Mar 29 '12 at 01:12
No, because unfortunately, each position ID is different, and the only way for us to determine rank is based on the letter. There's nothing numerical tying them — Pete, Mar 29 '12 at 01:20

score 2 · Accepted Answer · answered Mar 29 '12 at 01:29

This should give you the most control over it:

order by
  case left(positions.colleague_position_id, 1)
    when 'A' then 1
    when 'F' then 2
    when 'T' then 3
    when 'S' then 4
    when 'C' then 5
    else 6
  end, 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.

score 0 · Answer 2 · answered Mar 29 '12 at 01:17

0

How about removing the WildCard?

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

See Source: ORDER BY SPECIFIC VALUE

answered Mar 29 '12 at 01:17

John Woo

258,903
69
498
492

MySQL ORDER BY FIELD with %

2 Answers2

Linked