MySQL Sorting by substring of a field

user_1596143 0
Viewed 71

A very old DB couldn't be modified, and so instead of adding a "customType" field, the final user "invented" a way to categorize the items. In detail, if the description field:

  • starts with a * it's of type1
  • starts with a # it's of type2
  • starts with a ** it's of type3
  • otherwise, it's of type4

Now, I have been asked to generate some reports sorted by a "category" field but grouped by the type. Now, APART FROM SPLITTING THE QUERY IN 4 PARTS, and sure enough I can do it, I was wondering if I could do it in a single query.

If there would exist a "customType" field, the query would be stupid easy:

SELECT * FROM item ORDER BY customType, category

But unfortunately I cannot do it. My first try was something like

SELECT * FROM item ORDER BY SUBSTRING(description,2), category

But the problem is that I (correctly) get the items grouped by the type together, but the categories are not kept together. To be more clear, I would like to have an output like

aaaaa|cat1
ddddd|cat1
bbbbb|cat2
ccccc|cat2
#aaaa|cat5
#bbbb|cat5

But right now I am getting

aaaaa|cat1
bbbbb|cat2
ccccc|cat2
ddddd|cat1
#aaaa|cat5
#bbbb|cat5

As you can see, the cat1/cat2 are mixed up, the categories should stay together

sorting mysql
1 Answers

You may try ordering by a CASE expression:

SELECT *
FROM item
ORDER BY
    CASE WHEN LEFT(description, 2) = '**' THEN 3
         WHEN LEFT(description, 1) = '*'  THEN 1
         WHEN LEFT(description, 1) = '#'  THEN 2
         ELSE 4 END,
    category;

Note that we check for descriptions starting with ** before checking for starting with *. This is to avoid misclassifying starts with ** in the same bucket as just starts with *.

user_18632290
Related