Faced the question of whether ORDER BY can be sorted in MySQL according to the principle that I will describe below. There is a column that indicates the category of visitor. Categories are:

A +, A, B, C

etc.

Standardly SELECT * FROM db ORDER BY cat gives the result:

A, A +, B, C

I need А+ be the first category in sorting.

What can you advise?

    2 answers 2

    There are various options. For example:

    1. Change column type "category" to ENUM: category ENUM('A+', 'A', 'B', 'C', 'D') - then the normal sort by ORDER BY category ASC will use the specified order. This will work only if the options are clearly limited and known in advance.

    2. Use CASE WHEN ... END to sort:

       SELECT * FROM myTable ORDER BY (CASE WHEN category='A+' THEN 0 ELSE ASCII(UCASE(category) END) ASC
    • Aleks, thank you for your reply. You helped me :) - Eugene
    • @ Eugene Option 3: order by reverse(category) backwards + A will be the first, the remaining letters as usual :) - Mike

    There is another option that will work better if your system expands, and also corresponds to the third normal form . Create an additional categories table, in which we make the id and cat_name fields. In this table we put all possible categories. In the table myTable, instead of the category field, we make the cat_id field, into which the number of the required category is entered. After this, we make a connection from the categories.id field in the one-to-many field of myTable.cat_id. The link will ensure that records with a non-existent category cannot be added to the myTable table.

    Now about the selection with sorting. I may be mistaken with the features of MySql syntax, but the essence is this:

     SELECT myTable.id, categories.cat_name FROM myTable JOIN categories ON myTable.cat_id=categories.id ORDER BY myTable.cat_id

    In this case, the categories are sorted in the order in which they are entered into the categories table.

    • I chose, in my opinion, the easiest option from the proposed. Made column type "category" ENUM . Since I know what categories are and what they are not added, I wrote my categories in the Value in the order that the filter should be (A +, A, B, C ...) and everything works as it should. - Eugene
    • one
      @ Eugene, nevertheless, be sure to read about normalization. This will help you when your databases are more developed, large and large. - maestro
    • Thanks for the advice, be sure to read! I am new to this business, called programming :) Grateful for any instruction! - Eugene