MySQL ORDER BY FIELD with%
I am trying to ORDER BY FIELD using wildcard and with no success:
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 field colleague_position_id
has a text ID generated by our MIS system and I would like the positions starting with A to be displayed first, F to display the second, etc. etc.
Any help you could provide would be greatly appreciated.
Thank!
source to share
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 desired position (in this case at the end). The function field()
will return 0
for non-matching values and place them at the beginning of the result set, even before those that start with A
.
Alternatively, you can also order positions.colleague_position_id
as I did in the example, so for many positions.colleague_position_id
that start with the same letter, they will still be fine.
source to share