10 MySQL Techniques to Sort Custom Role Levels (Make 2 > 3)

A MySQL table dept stores employee roles with a numeric level column where 1 = regular employee, 2 = manager, and 3 = vice‑manager. The schema is frozen, so the requirement is to sort the data as 2 > 3 > 1 without altering existing hard‑coded values.

Method 1 – Add a helper column

Create a new column that defines the desired order, update its values, and sort by it.

alter table dept add new_level int default 1;
update dept set new_level=1 where level=1;
update dept set new_level=3 where level=2;
update dept set new_level=2 where level=3;
select id, level, name from dept order by new_level desc;

Method 2 – Use FIELD()

Leverage MySQL's FIELD() function to specify the custom order directly.

SELECT * FROM dept ORDER BY FIELD(`level`, 2, 3, 1);

Method 3 – Use FIND_IN_SET()

Convert the desired order into a comma‑separated string and sort by the position of each level value.

SELECT * FROM dept ORDER BY FIND_IN_SET(`level`, '2,3,1');

Method 4 – Use CASE WHEN

Map each original level to a new ranking with a CASE expression.

SELECT * FROM dept ORDER BY (CASE WHEN level=1 THEN 1 WHEN level=2 THEN 3 WHEN level=3 THEN 2 END) DESC;

Method 5 – Subquery with CASE

Generate a derived table that assigns a temporary rank and join it back for ordering.

SELECT a.* FROM dept a,
     (SELECT id, name,
             CASE WHEN level='1' THEN 'a'
                  WHEN level='2' THEN 'c'
                  WHEN level='3' THEN 'b' ELSE '0' END AS t
      FROM dept) b
WHERE a.id = b.id
ORDER BY b.t DESC;

Method 6 – UNION ALL

Combine three separate selects, each filtered by a specific level, and let the UNION order the rows.

SELECT * FROM dept WHERE level=2
UNION ALL
SELECT * FROM dept WHERE level=3
UNION ALL
SELECT * FROM dept WHERE level=1;

Method 7 – Hash‑based formula

Apply the original arithmetic expression (4‑level)%3 (or variations) to derive a sortable key.

SELECT (4‑level)%3, (7‑level)%3, level, id FROM dept;

Method 8 – 3*N+1 pattern

Use a random multiplier to generate a pseudo‑hash that respects the desired order.

SELECT * FROM dept ORDER BY (3*(FLOOR(RAND()*100)+1)+1‑level)%3 DESC, id;

Method 9 – Absolute‑value function

Model the ranking as a piecewise linear function -3*ABS(level‑2)/2 + level/2 + 2 and sort by its result.

SELECT * FROM dept ORDER BY -3*ABS(level‑2)/2 + level/2 + 2 DESC;

Method 10 – Approximate fit with ABS()

Use a simpler absolute‑value expression centered at 2.1 to achieve the same ordering.

SELECT * FROM dept ORDER BY -ABS(level‑2.1) DESC;

All ten approaches produce the same ordering where level 2 (vice‑manager) appears before level 3 (manager) and both precede level 1 (regular employee). The choice depends on readability, performance, and whether schema changes are permissible.