MySQL order by mixed ASC/DESC in the same column

Question

I am trying to sort product bin locations from a database by both ASC and DESC order in the same column, to allow a warehouse picker to weave through the warehouse isles to pick product. In other words, when a picker gets a batch of orders to pick from the warehouse, the system needs to start them at the front of isle 1, then order picks going down the isle to the end. Then it would jump them over to the end of isle 2 (instead of to the beginning), and they would work their way toward the front of isle 2, then start at the front of isle 3 and so on.

The bin locations are in the format: ISLE - BAY - SHELF - SLOT/BIN Here is an example data table of bin locations to pick:

1-0-A-01
1-1-D-06
1-2-E-10
1-2-E-11
1-10-A-01
2-1-D-02
2-1-C-12
2-5-F-01
3-5-A-12
3-6-D-01
4-5-A-02
4-5-A-03
4-5-B-10

I need to do a SQL query and pull the locations and order them like this:

1-0-A-01
1-1-D-06
1-2-E-10
1-2-E-11
1-10-A-01
2-5-F-01
2-1-D-02
2-1-C-12
3-5-A-12
3-6-D-01
4-5-B-10
4-5-A-03
4-5-A-02

Is it possible to do this with just a SQL query?

if those coordinates are a string in a text/varchar field, then no, there is no simple query you can run to sort them properly. YOu'd need to normalize the DB so that each coordinate component is in its OWN field, then a simple order by isle, bay, shelf, slot will do what you want. — Marc B
You can try ordering by their respective parts: Order by SUBSTRING_INDEX(SUBSTRING_INDEX(yourColumn, '-', 1), ' ', -1), SUBSTRING_INDEX(SUBSTRING_INDEX(yourColumn, '-', 2), ' ', -1), ... etc. But that could potentially be slow — Siyual

spencer7593 spencer7593 · Accepted Answer · 2014-06-20T19:32:51

Yes, this can be done within a SQL query, though the syntax is non-trivial.

You'd first need expressions to "split" the ISLE-BAY-SHELF into separate components, and then you use those expressions in an ORDER BY clause.

For MySQL

Some example expressions, put into the SELECT list just so we can see what they return:

SELECT SUBSTRING_INDEX('1-10-A-01','-',1)+0 AS ISLE
     , SUBSTRING_INDEX(SUBSTRING_INDEX('1-10-A-01','-',2),'-',-1)+0 AS BAY
     , SUBSTRING_INDEX(SUBSTRING_INDEX('1-10-A-01','-',3),'-',-1) AS SHELF
     , SUBSTRING_INDEX('1-10-A-01','-',-1)+0 AS `SLOT/BIN`

These expressions are based on the assumption that there will always be three dashes, and always in the format numeric-numeric-whatever-numeric.

Given the sample data, we could check if the ISLE component is even or odd, and then order the BAY either ascending or descending based on that. But that's probably not what you want, if one aisle is skipped, if we skipped aisle 2 entirely, and did just aisles 1 and 3.

CREATE TABLE ibss (ibss VARCHAR(20));
INSERT INTO ibss (ibss) VALUES 
('1-0-A-01')
,('1-1-D-06')
,('1-2-E-10')
,('1-2-E-11')
,('1-10-A-01')
,('2-5-F-01')
,('2-1-D-02')
,('2-1-C-12')
,('3-5-A-12')
,('3-6-D-01')
,('4-5-B-10')
,('4-5-A-03')
,('4-5-A-02');


SELECT i.ibss
     , SUBSTRING_INDEX(i.ibss,'-',1)+0 AS ISLE
     , SUBSTRING_INDEX(SUBSTRING_INDEX(i.ibss,'-',2),'-',-1)+0 AS BAY
     , SUBSTRING_INDEX(SUBSTRING_INDEX(i.ibss,'-',3),'-',-1) AS SHELF
     , SUBSTRING_INDEX(i.ibss,'-',-1)+0 AS `SLOT/BIN`
     , (SUBSTRING_INDEX(i.ibss,'-',1)+0) MOD 2 AS odd_or_even_isle
     , IF((SUBSTRING_INDEX(i.ibss,'-',1)+0) MOD 2
         ,SUBSTRING_INDEX(SUBSTRING_INDEX(i.ibss,'-',2),'-',-1)+0,NULL
       ) AS odd_bay
     , IF((SUBSTRING_INDEX(i.ibss,'-',1)+0) MOD 2
         ,NULL,SUBSTRING_INDEX(SUBSTRING_INDEX(i.ibss,'-',2),'-',-1)+0
       ) AS even_bay
  FROM ibss i
 ORDER BY -- ascending by ISLE
         SUBSTRING_INDEX(i.ibss,'-',1)+0 ASC
         -- ascending by BAY if ISLE is odd
       , IF((SUBSTRING_INDEX(i.ibss,'-',1)+0) MOD 2
           ,SUBSTRING_INDEX(SUBSTRING_INDEX(i.ibss,'-',2),'-',-1)+0,NULL
         ) ASC
         -- descending by BAY if ISLE is even
       , IF((SUBSTRING_INDEX(i.ibss,'-',1)+0) MOD 2
           ,NULL,SUBSTRING_INDEX(SUBSTRING_INDEX(i.ibss,'-',2),'-',-1)+0
         ) DESC
         -- ascending by shelf
       , SUBSTRING_INDEX(SUBSTRING_INDEX(i.ibss,'-',3),'-',-1)
         -- ascending by SLOT/BIN
       , SUBSTRING_INDEX(i.ibss,'-',-1)+0

Again, the ascending/descending ordering by BAY is going to depend on whether ISLE is even or odd, not on whether this is an alternating aisle. (This behavior might be desirable if you want the pickers moving the same direction down the aisles, and not in opposite directions.) To get the order changed based on an "aisle change", then we'd need to add some additional logic.

    ibss         ISLE     BAY  SHELF   SLOT/BIN  odd_or_even_isle  odd_bay  even_bay  
    ---------  ------  ------  ------  --------  ---------------- -------  ----------
    1-0-A-01        1       0  A              1                1        0      (NULL)
    1-1-D-06        1       1  D              6                1        1      (NULL)
    1-2-E-10        1       2  E             10                1        2      (NULL)
    1-2-E-11        1       2  E             11                1        2      (NULL)
    1-10-A-01       1      10  A              1                1       10      (NULL)
    2-5-F-01        2       5  F              1                0   (NULL)           5
    2-1-C-12        2       1  C             12                0   (NULL)           1
    2-1-D-02        2       1  D              2                0   (NULL)           1
    3-5-A-12        3       5  A             12                1        5      (NULL)
    3-6-D-01        3       6  D              1                1        6      (NULL)
    4-5-A-02        4       5  A              2                0   (NULL)           5
    4-5-A-03        4       5  A              3                0   (NULL)           5
    4-5-B-10        4       5  B             10                0   (NULL)           5

MySQL order by mixed ASC/DESC in the same column

2 Answers