Sort values that contain letters and symbols in a custom order

Question

Can you change the MySQL sort by function? I am trying to sort my values according to an arbitrary order.

Currently looking for ways to inject a function that might help me out here short of adding a column and modifying the import.

This is the order I want:

AAA
AA+
AA
AA-
A+
A
A-
BBB+
BBB
BBB-
BB+
BB
BB-
B+
B
B-
CCC+
CCC
CCC-
CC

This is my result using sort by:

A
A+
A-
AA
AA+
AA-
AAA
B
B+
B-
BB
BB+
BB-
BBB
BBB+
BBB-
C
CC
CCC
CCC+
CCC-

EDIT: Attempting but getting syntax errors:

CREATE FUNCTION sortRating (s CHAR(20))
RETURNS INT(2)
DECLARE var INT
CASE s
    WHEN 'AAA' THEN SET var = 1
    WHEN 'AA+' THEN SET var = 2
    ELSE
        SET VAR = 3
END CASE
RETURN var
END;

Is the set of values finite? If so, it would be easiest to create a lookup table with assigned rank values. — Michael Berkowski, Mar 02 '15 at 16:32
yes—set of values is finite. was just looking into writing my first stored procedure with a whole bunch of case statements. @MichaelBerkowski - what do you mean by a lookup table? — ovatsug25, Mar 02 '15 at 16:33

score 5 · Accepted Answer · answered Mar 02 '15 at 16:56

This is possible using the following syntax:

ORDER BY FIELD(<field_name>, comma-separated-custom-order)

for instance, if the expression you want to order by is called rating, then your ORDER BY clause would read:

ORDER BY FIELD(rating, 'AAA', 'AA+', 'AA', 'AA-', 'A+', 'A', 'A-', 
                       'BBB+', 'BBB', 'BBB-', 'BB+', 'BB', 'BB-', 
                       'B+', 'B', 'B-', 'CCC+', 'CCC', 'CCC-', 'CC')

Here's documentation on the FIELD FUNCTION

this function is great! now i only need to append this string to a zf2 query. :) — ovatsug25, Mar 02 '15 at 17:25

Salman A · Answer 2 · 2015-03-02T21:13:38.223

1

I see a pattern here:

BBB+
BBB
BBB-
BB+
BB
BB-
B+
B
B-

Think of each character as a column and sort each column in this order:

Letters
+
empty string
-

SELECT rating
FROM test
ORDER BY
         MID(rating, 1, 1),
    CASE MID(rating, 2, 1) WHEN '+' THEN 2 WHEN '' THEN 3 WHEN '-' THEN 4 ELSE 1 END, 
    CASE MID(rating, 3, 1) WHEN '+' THEN 2 WHEN '' THEN 3 WHEN '-' THEN 4 ELSE 1 END,
    CASE MID(rating, 4, 1) WHEN '+' THEN 2 WHEN '' THEN 3 WHEN '-' THEN 4 ELSE 1 END

SQL Fiddle

edited Mar 02 '15 at 21:13

answered Mar 02 '15 at 17:23

Salman A

262,204
82
430
521

interesting! thanks! other function was easier but this is pretty great and instructive – ovatsug25 Mar 02 '15 at 17:25

Sort values that contain letters and symbols in a custom order

2 Answers2

Linked