sql - MySQL order by mixed ASC/DESC in the same column -

sql - MySQL order by mixed ASC/DESC in the same column -

i trying sort product bin locations database both asc , desc order in same column, allow warehouse picker weave through warehouse isles pick product. in other words, when picker gets batch of orders pick warehouse, scheme needs start them @ front end of isle 1, order picks going downwards isle end. jump them on end of isle 2 (instead of beginning), , work way toward front end of isle 2, start @ front end of isle 3 , on.

the bin locations in format: isle - bay - shelf - slot/bin here illustration info table of bin locations 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 sql query , pull locations , order them 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 possible sql query?

yes, can done within sql query, though syntax non-trivial.

you'd first need expressions "split" isle-bay-shelf separate components, , utilize expressions in order clause.

for mysql

some illustration expressions, set select list can see return:

select substring_index('1-10-a-01','-',1)+0 isle , substring_index(substring_index('1-10-a-01','-',2),'-',-1)+0 bay , substring_index(substring_index('1-10-a-01','-',3),'-',-1) shelf , substring_index('1-10-a-01','-',-1)+0 `slot/bin`

these expressions based on assumption there 3 dashes, , in format numeric-numeric-whatever-numeric.

given sample data, check if isle component or odd, , order bay either ascending or descending based on that. that's not want, if 1 aisle skipped, if skipped aisle 2 entirely, , did aisles 1 , 3.

create table ibss (ibss varchar(20)); insert 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 isle , substring_index(substring_index(i.ibss,'-',2),'-',-1)+0 bay , substring_index(substring_index(i.ibss,'-',3),'-',-1) shelf , substring_index(i.ibss,'-',-1)+0 `slot/bin` , (substring_index(i.ibss,'-',1)+0) mod 2 odd_or_even_isle , if((substring_index(i.ibss,'-',1)+0) mod 2 ,substring_index(substring_index(i.ibss,'-',2),'-',-1)+0,null ) odd_bay , if((substring_index(i.ibss,'-',1)+0) mod 2 ,null,substring_index(substring_index(i.ibss,'-',2),'-',-1)+0 ) even_bay ibss order -- ascending isle substring_index(i.ibss,'-',1)+0 asc -- ascending bay if isle odd , if((substring_index(i.ibss,'-',1)+0) mod 2 ,substring_index(substring_index(i.ibss,'-',2),'-',-1)+0,null ) asc -- descending bay if isle , if((substring_index(i.ibss,'-',1)+0) mod 2 ,null,substring_index(substring_index(i.ibss,'-',2),'-',-1)+0 ) desc -- ascending shelf , substring_index(substring_index(i.ibss,'-',3),'-',-1) -- ascending slot/bin , substring_index(i.ibss,'-',-1)+0

again, ascending/descending ordering bay going depend on whether isle or odd, not on whether alternating aisle. (this behavior might desirable if want pickers moving same direction downwards aisles, , not in opposite directions.) order changed based on "aisle change", we'd need add together additional logic.

ibss isle bay shelf slot/bin odd_or_even_isle odd_bay even_bay --------- ------ ------ ------ -------- ---------------- ------- ---------- 1-0-a-01 1 0 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 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 12 1 5 (null) 3-6-d-01 3 6 d 1 1 6 (null) 4-5-a-02 4 5 2 0 (null) 5 4-5-a-03 4 5 3 0 (null) 5 4-5-b-10 4 5 b 10 0 (null) 5

mysql sql sql-order-by