Custom ordering SQL

Question

I want to order things in the order 1, 1a, 1b, 2, 2a, 2b, ... on a lot of places. I am considering the following options:

1: use an enum field for 1, 1a, 1b, 2, 2a, 2b, ... such that it will order nicely. Downside defining on different places an enum like that.

2: create an extra table with two fields. A string field with the values 1, 1a, 1b, 2, 2a, 2b, ... and an order index and union with that table everywhere that ordering is needed. Downside is lots of unions.

What options would you advise, or do you consider other options?

Thanks

When you say, "unions" I believe that you mean JOINs? Also, does the ordering follow a specific algorithm in all places? For example, are all of the values always <number><letter>? — Tom H
– Tom H, Commented Jan 11, 2016 at 13:45

Strawberry · Accepted Answer · 2016-01-11 13:45:42Z

2

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,string VARCHAR(12) NOT NULL);

INSERT INTO my_table (string) VALUES ('2b'),('1'),('2a'),('1b'),('1a'),('11');

SELECT * FROM my_table;
+----+--------+
| id | string |
+----+--------+
|  1 | 2b     |
|  2 | 1      |
|  3 | 2a     |
|  4 | 1b     |
|  5 | 1a     |
|  6 | 11     |
+----+--------+

SELECT * FROM my_table ORDER BY string + 0,string;
+----+--------+
| id | string |
+----+--------+
|  2 | 1      |
|  5 | 1a     |
|  4 | 1b     |
|  3 | 2a     |
|  1 | 2b     |
|  6 | 11     |
+----+--------+

answered Jan 11, 2016 at 13:45

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

maesk Over a year ago

Thanks for this nice solution!

Tejas Patil · Accepted Answer · 2016-01-11 13:47:58Z

0

Try sorting using the below code :

SELECT * FROM t ORDER BY st REGEXP '^[[:alpha:]].*', st+0, st ;

Hope this helps

answered Jan 11, 2016 at 13:47

Tejas Patil

526 bronze badges

1 Comment

Pred Over a year ago

Why and how this code help? Please explain what it is doing, why is it better than the ones the OP already listed in the question. In this form, this is not a good answer. Please read How to Answer in help center for more information. (As a side note, this solution will not work on large data sets, since it prevents the server to use any indexes to perform the sorting which will lead to a file sort.)

Collectives™ on Stack Overflow

Custom ordering SQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related